I need assistance to enhance the method I've implemented. Here are the details.
I have multiple excel spreadsheets (xls, originally written on Excel 2007 or 2010) that have 3 main macros that I want to automate to run sequentially.
I have one file for each one of our remote locations, each remote locations is divided in three main areas
so I have all those files saved on a shared drive as follows:
Area A (folder)
Office/Plant A.xls
Office/Plant B.xls
Office/Plant C.xls
Area B
Office/Plant D.xls
Office/Plant X.xls
I have created a unique VBS per each xls file and then execute all the scripts from a bat file.
The code for each VBS file is basically the same but I have "hard coded" the paths and the file names...
is there a way I can provide the three paths and the names of all the files as variables so the same code can loop through the list of files and execute the three macros in sequence? (the names of the macros are always the same)
This is the code I am currently using: (the files are protected)
Dim xlApp
Dim xlWkb
Set xlApp = CreateObject("excel.application")
Set xlWkb = xlApp.Workbooks.Open("P:\Area A\Office/Plant A.xls")
'xlWkb.RunAutoMacros 1 'xlautoopen ???
xlApp.Run ("unprotect_spreadsheets")
xlApp.Run ("Download_Data")
xlWkb.Save
xlApp.Run ("Protect_SpreadSheets")
xlWkb.Save
xlWkb.Close false
Set xlWkb = Nothing
xlApp.Quit
Set xlApp = Nothing
WScript.Echo "Finished: Office/Plant A.xls"
Can you suggest something? I want to start initializing a list of variables and paths because not all the XLS files on the selected directories are valid entries for the script...
Thoughts?