Running Excel Macro From VBS (with 2 versions of excel)

[jfried]

I'm trying to automate excel to run a macro contained inside a workbook. From reading various threads, it seems that this should be simple to do using windows task scheduler and VBscript.

However, what seems to be throwing a wrench into the mix is that I have both Excel 2010 and Excel 2003 installed on this machine, Excel 2003 is in the default location. This file however, needs to be run in Excel 2010 (too big). My first issue was that using Set objExcel = CreateObject("Excel.Application") would launch 2003, and I was unable to change this behaviour using regserver. However, I was able to get the file to open in 2010 by using GetObject.

The problem now is, when I run it, I get an error message saying that the Macro may not be available or all Macros are disabled. I've set Excel to allow all macros, and added the location to the trusted location.


Set objExcel = GetObject("D:\myfile.xlsm")
objExcel.Application.Visible = True

objExcel.Application.Run ("CreateRepotversion2()")
objExcel.ActiveWorkbook.Close

objExcel.Application.Quit
WScript.Echo "Finished."

[His Nibbs]

CreateRepotversion2. Spelling mistake? Is the subroutine in a module? Is it Public?

[jfried]

CreateRepotversion2. Spelling mistake? Is the subroutine in a module? Is it Public?

Not a spelling mistake, (or more accurately, originally was, but the spelling does match).

The sub is in a module, and I don't know if it's public. How do I go about verifying that?

[His Nibbs]

The subroutine has Public scope (visible to all modules/procedures including external VBScript call) if it has the word 'Public' in front of the subroutine name or nothing:

Public Sub My_Subroutine_Name()

I tried the GetObject method of opening a specific workbook shown in your code and also got a 'macro not found' error.

I would try the CreateObject method again, specifying the class name of the required Excel version - see http://support.microsoft.com/kb/214388.

[jfried]

Unfortunately, you can't specify an excel version beyond 2003.... and despite numerous attempts, I haven't been able to specify that I want Excel 2010 to be the default... therefore it seems that I have to use the GetObject method.

Is there any way to do this, or to specify an excel version beyond 2003?