question

EddieCler-6236 avatar image
0 Votes"
EddieCler-6236 asked

VBScript Open Excel applicaion and check to see if file is open

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.

office-js-devoffice-scripts-excel-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

0 Answers