Migrating VBA Excel Code to C#.Net

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
chokkaStudentAsked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
try this
Dim wkb As Object = oXL.Workbooks.Open("C:\FinalCSVReader_src\ImportCSV.xls")

Open in new window

0
 
LordWabbitCommented:
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
 
aikimarkCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
chokkaStudentAuthor Commented:
@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
 
chokkaStudentAuthor Commented:
@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
 
LordWabbitCommented:
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
 
LordWabbitCommented:
Oops in your case it's

oApp.Run("ImportCSV.xls!Module1.MyCaller");
0
 
aikimarkCommented:
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
 
chokkaStudentAuthor Commented:
@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
 
chokkaStudentAuthor Commented:
@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
 
chokkaStudentAuthor Commented:
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
 
chokkaStudentAuthor Commented:
Do i need to generate ActiveX Dll from Excel Work Book ? If so, How should i generate the Active X Dll ?
0
 
aikimarkCommented:
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
 
chokkaStudentAuthor Commented:
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
 
aikimarkCommented:
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
 
chokkaStudentAuthor Commented:
System.MissingMemberException was unhandled
  Message="Public member 'Open' on type 'ApplicationClass' not found."
  Source="Microsoft.VisualBasic"

Doesn't work.
0
 
aikimarkCommented:
there's a typo.  change the double periods to a single period.
0
 
chokkaStudentAuthor Commented:
@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
 
chokkaStudentAuthor Commented:
Thank you aikimark, It is working.
0
 
chokkaStudentAuthor Commented:
Thank you aikimark
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.