While I'm trying to schedule and trigger a macro of a sample test file, I could setup a .vbs file and a bat file that has following code that I learnt from a yt video. When I ran the batch file and checked my macro workbook to see if the changes are reflected, I get an error message saying 'workbook is locked for editing by me'. It doesn't make sense to me as I don't have any workbooks opened at the moment.
I do have cscript.exe on my machine
- 'Input Excel File's Full Path
- ExcelFilePath = "C:\Users\dev\TestMacro\Sample.xlsm"
- 'Input Module/Macro name within the Excel File
- MacroPath = "Module1.Test"
- 'Create an instance of Excel
- Set ExcelApp = CreateObject("Excel.Application")
- 'Do you want this Excel instance to be visible?
- ExcelApp.Visible = True 'or "False"
- 'Prevent any App Launch Alerts (ie Update External Links)
- ExcelApp.DisplayAlerts = False
- 'Open Excel File
- Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)
- 'Execute Macro Code
- ExcelApp.Run MacroPath
- 'Save Excel File (if applicable)
- 'Reset Display Alerts Before Closing
- ExcelApp.DisplayAlerts = True
- 'Close Excel File
- 'End instance of Excel
- 'Leaves an onscreen message!
- MsgBox "Your Automated Task successfully ran at " & TimeValue(Now), vbInformation