Solved

Migrating VBA Excel Code to C#.Net

Posted on 2013-06-24
20
532 Views
Last Modified: 2013-06-26
An Expert from EE helped me to import csv file which has multiple row group of records to sql / excel database.

He wrote a VBA Macro Code inside an Excel sheet where he placed a button. On click event of that button, it imports CSV file to a neatly arranged Excel Sheet.

I am writing a C# code which does complete flow of Importing CSV file to SQL Database and do other functions.

I am trying to reuse VBA Macro code to my C#.Net.
ImportCSV.xls
Test-6619289--5-30-2013.csv
0
Comment
Question by:chokka
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
  • 3
20 Comments
 
LVL 11

Expert Comment

by:LordWabbit
ID: 39274444
Well you can't really reuse the VBA code in a C# project, but you can use it as a guide to what you need to write in C#.  The first step would be to add a reference to Excel in your project, I always find adding

using mso = Microsoft.Office.Interop;

as a using makes life a lot easier.
Then you create an excel application, using it's workbooks collection open up the workbook (the excel file) and from there you can start manipulating the worksheets, cells etc.
Oh yeah, right after creating the excel application object call .visible = true otherwise you won't see anything.  Also keep an eye on how many excel.exe processes you make, they get out of hand quickly when debugging.

Hope this helps!
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39274675
If your VBA code is inside the Workbook (natively or as an Add-in), your C# code should be able to invoke it.  You would instantiate an Excel application object, open a workbook and call the routine by name.
0
 

Author Comment

by:chokka
ID: 39275107
@aikimark - Yes, VBA Code is inside the Workbook.

Please take a look on the Excel Sheet and CSV file which i attached in the
Posting Id # 28166408

But i am not sure how to instantiate and call the routine by name
0
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 

Author Comment

by:chokka
ID: 39275113
@Lordwabbit. I am actually using the VBA Code as a guide and trying to rewrite the syntax in c#.net. I am not familiar with VBA Code which hurdles my migration work.

For example

There is a function inside VBA Code. I wonder whether this is a ArrayList or Function ??

Type InvoiceHeader
    Invoice_Date As Date
    Invoice_Number As Long
    Account_Number As Long
    DEA_Number As String
    Sales_Rep As Long
End Type
0
 
LVL 11

Expert Comment

by:LordWabbit
ID: 39275177
VBA's version of a struct

If you want to go the other route and call the macro function here is some code to do it

            mso.Excel.Application oApp = new mso.Excel.Application();
            oApp.Visible = true;            
            mso.Excel.Workbook oWb = oApp.Workbooks.Open(@"C:\eatme\JulyFigures.xlsm");
            oApp.Run("JulyFigures.xlsm!ThisWorkbook.runAllOfThem");

The ThisWorkbook part is important, won't work without it.
0
 
LVL 11

Expert Comment

by:LordWabbit
ID: 39275198
Oops in your case it's

oApp.Run("ImportCSV.xls!Module1.MyCaller");
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39275285
Here is a VBScript example I just tested with your workbook and CSV file.  You wouldn't need to make the workbook visible and you would need to close (with save=true) the workbook.

set oXL=createobject("Excel.Application")
oxl.visible = true
set wkb=oXL.workbooks.Open("C:\Users\AikiMark\Downloads\ImportCSV.xls")
oxl.application.run "importcsv", "C:\Users\AikiMark\Downloads\Test-6619289--5-30-2013.csv"
'wkb.close true

Open in new window

0
 

Author Comment

by:chokka
ID: 39275771
@aikimark

I facing COM Library error at Dim wkb as New Workbook

Do i need to load any COM / COM+ Components.

Also in oXL.Application.Run("importcsv", " ") - importcsv is the name of the Excel Sheet or CSV Sheet ?


 Dim oXL As New Application
        oXL = CreateObject("Excel.Application")
        oXL.Visible = True

        Dim wkb As New Workbook
        wkb = oXL.Workbooks.Open("C:\FinalCSVReader_src\ImportCSV.xls")
        oXL.Application.Run("importcsv", "C:\FinalCSVReader_src\Test-6619289--5-30-2013.csv")
        'wkb.close true

Open in new window

0
 

Author Comment

by:chokka
ID: 39275789
@LordWabbit and @aikimark

System.Runtime.InteropServices.COMException :
Retrieving the COM class factory for component with CLSID {00020819-0000-0000-C000-000000000046} failed due to the following error: 80040154.

  Dim oWb As New Workbook()
0
 

Author Comment

by:chokka
ID: 39275795
How to call workbook ? Do i need add any COM / COM+ Component. Do i need to add any dll to my .Net Project Reference
0
 

Author Comment

by:chokka
ID: 39275807
Do i need to generate ActiveX Dll from Excel Work Book ? If so, How should i generate the Active X Dll ?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39275905
Define them as objects or variants.  The CreateObject() function will instantiate them correctly.  The way you've written the code in .Net requires a reference to the Excel library/namespace.
0
 

Author Comment

by:chokka
ID: 39275930
Copied the whole form page. On Button event, i am invoking this code.
Where you want me to instantiate the object ??


Imports System.IO
Imports System.IO.StreamReader
Imports System.IO.StreamWriter

Imports Microsoft.Office.Interop.Excel


Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        ' Code by LordWabbit
        'Dim oApp As New Application()
        'oApp.Visible = True
        'Dim oWb As New Workbook()
        'oWb = oApp.Workbooks.Open("C:\FinalCSVReader_src\ImportCSV.xls")
        'oApp.Run("ImportCSV.xls!Module1.MyCaller")

        'Code by aikimark
        Dim oXL As New Application
        oXL = CreateObject("Excel.Application")
        oXL.Visible = True

        Dim wkb As New Workbook
        wkb = oXL.Workbooks.Open("C:\FinalCSVReader_src\ImportCSV.xls")
        oXL.Application.Run("importcsv", "C:\FinalCSVReader_src\Test-6619289--5-30-2013.csv")
        wkb.Close(True)


    End Sub
End Class

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 39276632
Based on this reference:
http://social.msdn.microsoft.com/Forums/vstudio/en-us/df02c6d2-e1b5-4731-bb04-2674aed789de/faq-how-do-i-use-excel-automation-in-vbnet

Imports System.IO
Imports System.IO.StreamReader
Imports System.IO.StreamWriter
Imports Microsoft.Office.Interop.Excel


Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'Code by aikimark
        Dim oXL As Object = CreateObject("Excel.Application")
        Dim wkb As Object = oXL..Open("C:\FinalCSVReader_src\ImportCSV.xls")
        oXL.Visible = True

        oXL.Application.Run("importcsv", "C:\FinalCSVReader_src\Test-6619289--5-30-2013.csv")
        wkb.Close(True)

    End Sub
End Class

Open in new window

0
 

Author Comment

by:chokka
ID: 39276718
System.MissingMemberException was unhandled
  Message="Public member 'Open' on type 'ApplicationClass' not found."
  Source="Microsoft.VisualBasic"

Doesn't work.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39276727
there's a typo.  change the double periods to a single period.
0
 

Author Comment

by:chokka
ID: 39278064
@aikimark.

If there is a Syntax error, Application will not build.  I noticed that error.

I am getting this message after handling the error.

System.MissingMemberException was unhandled
  Message="Public member 'Open' on type 'ApplicationClass' not found."
  Source="Microsoft.VisualBasic"


  Dim oXL As Object = CreateObject("Excel.Application")
        Dim wkb As Object = oXL.Open("C:\FinalCSVReader_src\ImportCSV.xls")
        oXL.Visible = True
        oXL.Application.Run("importcsv", "C:\FinalCSVReader_src\Test-6619289--5-30-2013.csv")
        wkb.Close(True)

Open in new window

0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 39278098
try this
Dim wkb As Object = oXL.Workbooks.Open("C:\FinalCSVReader_src\ImportCSV.xls")

Open in new window

0
 

Author Comment

by:chokka
ID: 39278197
Thank you aikimark, It is working.
0
 

Author Closing Comment

by:chokka
ID: 39278251
Thank you aikimark
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question