Solved

Migrating VBA Excel Code to C#.Net

Posted on 2013-06-24
20
519 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

896 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

14 Experts available now in Live!

Get 1:1 Help Now