personal.xls

snapper1969 · Post by **snapper1969** » Tue Jan 31, 2012 10:31 am

Hi,

I have a macro saved to personal.xls but when I try to run it via a scheduler it says that the macro can't be found. Is there a problem with running macros in personal.xls?.

Thx,

John

Post by **Marcus Tettmar** » Tue Jan 31, 2012 11:05 am

Try specifying the following for the macro name:

PERSONAL.XLS!Name of your Macro here

snapper1969 · Post by **snapper1969** » Tue Jan 31, 2012 3:38 pm

mtettmar wrote:Try specifying the following for the macro name:

PERSONAL.XLS!Name of your Macro here

Hi Marcus, the code is below:

Code: Select all

BSTART
Sub OpenAndRun(xlFileName, MacroName)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = true
Set xlBook = xlApp.WorkBooks.Open(xlFileName)
xlApp.Run(MacroName)
End Sub
VBEND
VBRun>OpenAndRun,l:\test.xls,PERSONAL.XLS!Autofilter

I can see the personal.xls macro in test.xls. When I run the script I get an error saying it can't find personal.xls and I can't see personal.xls macro in the test.xls. When I close test.xls I can see the personal.xls macro again. Any ideas?.

Thx,

John

Post by **Marcus Tettmar** » Wed Feb 01, 2012 11:12 am

Having spent some time googling this I don't think personal.xls is opened by default when Excel is instantiated as COM object. Try opening personal.xls as well, e.g.:

VBRun>OpenAndRun,C:\Users\User\AppData\Roaming\Microsoft\Excel\XLSTART\personal.xlsb,TestMacro

But I guess you might actually need to modify the VBScript function to open both personal.xlsb and your test.xls doc if you are wanting to affect something inside test.xls

It might be easier to move the macro to test.xls. Or you could recode it in VBScript and run directly inside Macro Scheduler.

snapper1969 · Post by **snapper1969** » Wed Feb 01, 2012 12:14 pm

mtettmar wrote:Having spent some time googling this I don't think personal.xls is opened by default when Excel is instantiated as COM object. Try opening personal.xls as well, e.g.:

VBRun>OpenAndRun,C:\Users\User\AppData\Roaming\Microsoft\Excel\XLSTART\personal.xlsb,TestMacro

But I guess you might actually need to modify the VBScript function to open both personal.xlsb and your test.xls doc if you are wanting to affect something inside test.xls

It might be easier to move the macro to test.xls. Or you could recode it in VBScript and run directly inside Macro Scheduler.

Thanks Marcus,

I think I'll probably try and recode it in VBScript, better than opening another spreadsheet.

John