The macro is disabled if called using a vb script

Question

I am trying to call an excel macro using vbs. Here is a snippet of my code.

Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open("C:\Folder\Test_PO.xls") objExcel.Application.Visible = True objExcel.Application.Run "C:\Folder\Test_PO.xls!Data_Analysis" objExcel.ActiveWorkbook.Close objExcel.Application.Quit WScript.Echo "Finished." WScript.Quit 

Now the problem is that I can open the file, but the macro somehow turns off here and shows me 'macro may not be present or may be disabled' . I am sure that I am calling the correct macro name, but as soon as the file opens, the add-ons tab in which I configured the macro to run will not disappear. This does not open if I open the file manually, I see the tab and run the macro from the tab itself. Any suggestions, how could I overcome this problem and run a macro?

Answer 1

try it

 Dim objExcel, objWorkbook Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open("C:\Folder\Test_PO.xls") objExcel.Visible = True objExcel.Run "Data_Analysis" objWorkbook.Close objExcel.Quit Set objWorkbook = Nothing Set objExcel = Nothing WScript.Echo "Finished." WScript.Quit 

EDIT

If the macro is in the module, then this will help. If the macro is in a sheet, say Sheet1 then replace the line

 objExcel.Run "Data_Analysis" 

from

 objExcel.Run "sheet1.Data_Analysis" 

Followup

Try this code.

 Dim objExcel, objWorkbook, ad, FilePath Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True For Each ad In objExcel.AddIns If ad.Name = "Converteam.xla" Then FilePath = ad.Path & "\Converteam.xla" Exit For End If Next objExcel.Workbooks.Open (FilePath) Set objWorkbook = objExcel.Workbooks.Open("C:\Folder\Test_PO.xls") objExcel.Run "Data_Analysis_Converteam" objWorkbook.Close objExcel.Quit Set objWorkbook = Nothing Set objExcel = Nothing WScript.Echo "Finished." WScript.Quit 

EXPLANATION

When you use CreateObject , add-ins are not installed by default. See this link.

Subject: add-ins do not load when using CreateObject command in Excel

Link : http://support.microsoft.com/kb/213489/

You need to load the add-in, and then call the corresponding macro. Also, your macro name is not Data_Analysis , but Data_Analysis_Converteam

NTN

Answer 2

To add Siddhart to the answer, you can download the add-ons you need in VBScript:

 objExcel.RegisterXLL("analys32.xll") 'For XLL addins objExcel.Workbooks.Open(objExcel.LibraryPath & "\analysis\atpvbaen.xla") 'For standard XLA addins objExcel.Workbooks.Open("C:\Program Files\MyAddins\MyAddin.xla") 'for custom XLA addins