I need some help on an IF statement for the following code.
I would like to use a VBScript to open an excel file and execute a macro "RefreshAndEmail" within. This is operating on a file that may or may not already be open by me and is expected to run before I get to work.
If the file is already in use by me, I would like the script to connect to that excel instance. The code I have works if the file is closed, but errors out if the file is already open.
'Input Excel File's Full Path ExcelFilePath = "MyFileAndPath.xlsx" 'Input Module/Macro name within the Excel File MacroPath = "Module1.RefreshAndEmail" '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) wb.Save 'Reset Display Alerts Before Closing ExcelApp.DisplayAlerts = True 'Close Excel File wb.Close 'End instance of Excel ExcelApp.Quit 'Leaves an onscreen message! MsgBox "Your Automated Task successfully ran at " & TimeValue(Now), vbInformation
A couple notes:
The file is in my onedrive and is shared to a few users.
I would like to update the data in the file at 5:00 am every day. I have a windows task scheduled to run the VBScript.