VBS Problem auto-running VBA Macro

[msquared99]

Happy New Year Everyone!


I am trying to run a macro through Task Scheduler. I have the following VBS file (in NotePad) saved as *.vbs and it errors out:


'Write Sheet's full path here

strPath = "C:General UseCopy Daily Files.xlsm"


'Write the macro name including module

strMacro = "Sheet1.Module1 Copy_Files_To_New_Folder"


'Create an Excel instance and set visibility of the instance

Set objApp = CreateObject("Excel.Application")

'objApp.Visible = False (disabled to be able to view activity)


'Open workbook; Run Macro; Save Workbook with changes; Close; Quit Excel

Set wbToRun = objApp.Workbooks.Open(strPath)

objApp.Run wbToRun.name & "!" & strMacro

''wbToRun.Save (disabled - Internal Macro Sequence provides for this function)

''wbToRun.Close (Ditto)

''objApp.Quit (Ditto)


'Leaves an onscreen message!

''MsgBox "Code Running Was Successful!", vbInformation (Not Required)


I get the following error:


Line: 13

Char: 1

Error: Cannot run the macro "C:General UseCopy Daily Files.xlsm!Sheet1.Module1 Copy_Files_To_New_Folder" The macro may not be available in this workbook or all macros may be disabled.


Code: 800A03EC


I know the marco exists and when I open the file it is in my trusted marco folder.


All I am looking to do have the macro run when I want it to run (Example, I was on vacation 12/31/12 and wanted the macro to run on 1/1/13 at 7:00 AM).


Any suggestions?


Thanks,


Mike

 

[msquared99]

I have solved it.


Anyone who wants to autorun a macro can set it up in Schedule Task in Control Panel using the following code and save it using NotePad and RunMacro.vbs:


Option Explicit


Dim xlApp, xlBook


Set xlApp = CreateObject("Excel.Application")

'~~> Change Path here to your workbook below

Set xlBook = xlApp.Workbooks.Open("C:General UseCopy Daily Files.xlsm", 0, True)

'Change the name of your macro below

xlApp.Run "Copy_Files_To_New_Folder"

xlBook.Close

xlApp.Quit


Set xlBook = Nothing

Set xlApp = Nothing


WScript.Quit