Ben has attended:
Excel VBA Intro Intermediate course
Running a procedure for multiple workbooks.
Dear Sir/Madam
How do you run a procedure for multiple workbooks in different locations/folders?
i.e. we need to change a specific cell in 40 different excel spreadsheets all located in different folders. This cell has to increase in number by one every financial period.
Kind Regards,
Ben
RE: Running a procedure for multiple workbooks.
Ben
The computer needs to load the workbook into the system, then change the relevant cells and close
To do this
Workbooks.Open Filename:= The full path of the workbook eg. "c:\My Docs\book1.xls"
Then write the code to go in and change the relevant cell
Activeworkbook.Save
Activeworkbook.Close
If you want you can use a list of all the Workbook paths. Then create a variable eg.
Dim MyWorkbook as String
With which you replace filename path above.
Create a loop that loads each new Workbook name into the variable and then runs the above code.
Regards
Carlos
RE: Running a procedure for multiple workbooks.
I wonder whether it wouldn't be easier to have a handler for the Workbook.Open event, which checks the current date (Date function), calculates the appropriate financial period, and updates the cell accordingly.
/Roy