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

Eddie Cler 1 Reputation point
2021-10-05T14:17:30.977+00:00

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.

JavaScript API
JavaScript API
An Office service that supports add-ins to interact with objects in Office client applications.
937 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,707 questions
0 comments No comments
{count} votes