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...