Macro gets disabled if called using a vb script

vba, vbscript

Solution

Try this

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 a module then the above will help. If the macro is in a sheet say, Sheet1 then replace the line

objExcel.Run "Data_Analysis"

with

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`, the Add-Ins are not installed by default. Please see this link.

Topic: Add-ins do not load when using the CreateObject command in Excel

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

You have to load the Add-In and then call the relevant macro. Also the name of your macro is not `Data_Analysis` but `Data_Analysis_Converteam`

HTH

Problem

I am trying to call my 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 here is that i am able to open the file but the macro somehow gets disabled here and shows me `'macro may not be present or may be disabled'`. I am sure i am calling correct macro name but as soon as the file is opened the Add-ins tab where i had configured the macro to run from gets dissapeared.This does not open if i open the file manually , i can see the tab and run the macro from the tab itself. Any suggestions how i could overcome this problem and get the macro to run ?

Original source