Using a VBS Script file to run an excel macro

[mvolps22]

Good Morning,

Let me start by saying I'm new to VBS script files and have limited knowledge of macros. I've researched this for 4+ hours but I'm still struggling with it. My end goal is to setup a script to automatically run every morning that refreshes an excel spreadsheet via an add-in and saves it off into a network drive so everyone can access it.

I'll start with the easy part, the excel macro, all I want to do is have excel select the FactSet add-in and click refresh, simple enough to do just by clicking record macro and then follow the steps. I also wrote Application.Wait (Now + Time Value("00:00:30")) to add a 30 second delay because the add-in takes a few seconds to refresh. I then save the macro and close the spreadsheet.

Sub FactSetMacro()
'
'FactSetMacro
'
'Application.Wait (Now+TimeValue("00:00:30"))
'Application.DisplayAlerts = False
'
End Sub

The VBS script file in notepad is where it gets tricky for me, mostly because I don't really know what any of the functions do and they are just copied and adapted from various web searches. Ideally I'd like the script to:

• open excel (if needed),
• add a 60 second delay so the FactSet program, which opens automatically with the excel file, has a chance to open,
• run the macro,
• save the file as project_yyyymmdd.xlsm for backup
• as well as saving the project as project.xlsm in a different network drive.

Intuitively this seems like it should be a walk in the park but I need help. Here is what I started on but should probably be ignored because it doesn't work.

Option explicit

Dim x1App, x1Book

Set x1App = CreateObject("Excel.Application")
xet x1Book = x1App.Workbooks.Open("c:\shared\project.xlsm", 0, True)

WScript.Sleep 60000

x1App.Run "FactSetMacro"

x1Book.Close
x1App.Quit

Set x1Book = Nothing
Set x1App = Nothing

WScript.Echo "Finished."
WScript.Quit

I know I'm missing the language to save the file into a shared directory as well as creating a backup file but I can't find much on how to do that. Any help would appreciated.