Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

vba courses london - running procedure multiple workb

Forum home » Delegate support and help forum » Microsoft VBA Training and help » vba courses london - Running a procedure for multiple workbooks.

vba courses london - Running a procedure for multiple workbooks.

ResolvedVersion Standard

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

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

VBA tip:

Stop Display Alerts In Excel

When creating or deleting a Worksheet in Excel you don't want Excel to inform you "A file already exists....." Or "Do you want to save this file...".

To stop these alerts happening use the following line of code:

Application.DisplayAlerts = False

After the Create or Delete routine use:

Application.DisplayAlerts = True

View all VBA hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.