Solved

Migrating VBA Excel Code to C#.Net

Posted on 2013-06-24
20
517 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now