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")
xlApp.Run ("Protect_SpreadSheets")
xlWkb.Close false

Set xlWkb = Nothing
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...



VBS is rather inflexible and abstracted from the task at hand, so rather than use it to manage things why not just have a control workbook that triggers and controls the process? The error handling and audit could then be a lot more elegant and informative, and the fact that the objects are native makes things an awful lot easier.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.