• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

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
0
chokka
Asked:
chokka
  • 11
  • 6
  • 3
1 Solution
 
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
 
chokkaAuthor 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
chokkaAuthor 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
 
aikimarkCommented:
try this
Dim wkb As Object = oXL.Workbooks.Open("C:\FinalCSVReader_src\ImportCSV.xls")

Open in new window

0
 
chokkaAuthor Commented:
Thank you aikimark, It is working.
0
 
chokkaAuthor Commented:
Thank you aikimark
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 11
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now