Calling excel's vba subroutine from vb.net application

Question

I have created a vba subroutine "ImportTextFile" in excel workbook which takes arguments and I am trying to call that subroutine from my vb.net application according to the procedure given in

http://support.microsoft.com/kb/306682

but I am getting the error:

"Cannot read file from disk. Original error:Cannot run the macro 'ImportTextFile'. The macro may not be available in this workbook or all macro may be disable."

Even the macro window doesn't show that macro in the list.

Please Help!! 

Answer 1

Hi VixMixer,

Welcome to the MSDN Forum.

Please try this code snippet:

    Public Sub ExcuteMacro(ByVal filename As String, ByVal MacroName As String)
        Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        'Dim oRng As Excel.Range
        oXL = New Excel.Application
        oXL.Visible = True
        oWB = oXL.Workbooks.Open(filename)
        oWB.RunAutoMacros(1)
        oXL.Run(MacroName)
        'MacroName
        oXL.Quit()
        oXL = Nothing
        oWB = Nothing
    End Sub

I hope this will be helpful.

Best regards,

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi Vixmixer,

How about my code?

Did you get some errors?

I look forward you.

Best regards,

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Hi Mike,

code you have written is not working as we cannot create any object of excel.application by using new keyword..its giving error..

obviously if macro is not visible in macro list of excel...how can other application be able to recognize the macro..

is there any other option..??

can I create macro in vb.net in my application and run it with some excel worksheet at run time..??

Answer 4

Hi Mike,

code you have written is not working as we cannot create any object of excel.application by using new keyword..its giving error..

obviously if macro is not visible in macro list of excel...how can other application be able to recognize the macro..

is there any other option..??

can I create macro in vb.net in my application and run it with some excel worksheet at run time..??

Hi Vixmixer,

When you use new keyword, what error do you get? Do you import the Excel namespace?

Did you write your macro function in a class? If so, you cannot get it in the macro list. If you want to excute it, you need to move it to a module.

>>can I create macro in vb.net in my application and run it with some excel worksheet at run time

I don't suggest you to to that. Because when you can directly write the VB.net code to manipulate the Excel document rather than write a VBA.

I hope I am clear, if you have any concerns, please feel free to follow up and I will try my best to help you.

Best regards,

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

Hi Mike,

First of all intellisence property of vb.net doesn't show me keywords to write "New Excel.Application" ..if i forcefully write it...it showing me syntax error by underlining "Excel.Application" telling " 'New' cannot be used on an interface" :(

Answer 6

Hi Vixmixer,

 Do you import the Excel namespace?

Best regards,

---

Mike Feng
MSDN Community Support | Feedback to us
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 7

Hi Mike..

         Yes, I was importing excel namespace..but in the wrong way...I am Done now...:)

Actually we have to name the imported namespace...then only we can create object of excel application...

like..

Imports Exl = Microsoft.Office.Interop.Excel

Dim oExcel As Exl.Application

oExcel = new Exl.ApplicationClass

Anyways thanks for help mike...:)