×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

running macro on multiple workbooks

running macro on multiple workbooks

running macro on multiple workbooks

(OP)
I have many situations where I'm performing the same macro on 70 or 80 workbooks that are open at once.  I have no problems writing the macros to do what I want in each individual workbook, but what I'd like to be able to do is put a loop around my macro so that it would automatically churn through every open workbook and run the macro on it.  Does anyone know how to tell VBA that I want to do this?

Thanks!

RE: running macro on multiple workbooks

use a for loop along with workbooks.count and workbook(index).

RE: running macro on multiple workbooks

(OP)
Part of my macro closes each workbook after it applies whatever formatting is required.  Will this indexing method work when these workbooks begin closing?

RE: running macro on multiple workbooks

You need the macro to be located in a separate workbook -- either your Personal.xls (personal macro file) or else in a separate add-in file.  You create a workbook, place all your macros in it, and then in VB Editor, on the properties tab, set "Is Add-in" to true.  This hides the sheets view and the file is only visible in the VB Editor window. (This is how your personals.xls works too, but mine used to get really full!)

RE: running macro on multiple workbooks

(OP)
Oh yeah, I'm not running this from each individual workbook that I'm modifying.  It's in my personal.xls.  My concern is that if I index 20 workbooks at the beginning of the loop and start counting from one closing workbooks as I go, my loop willeventually go out of range.  

For instance, during the first loop through, the macro will open the workbook at index number 1, and modify it.  Then, it will close that workbook and increment the loop index number.  This means that there are now 19 available open workbooks that are indexed and the loop counter is at 2.  The next loop, it opens and modifies index #2, deletes that file bringing the number of open workbooks to 18, and increments the counter to 3.

At about the halfway point, it will be trying to open index number 11, but it will have closed 10 workbooks, so there is no index number 11 available, and the subscript goes out of range and everything comes to screeching halt.

I'm thinking of just running two loops.  The first does the modification on all of the files, and the second closes all of the files. I just wonder if there's a more efficient way to do it.

RE: running macro on multiple workbooks

(OP)
Wait a second.  If I loop from the highest index number to the lowest, my subscript will never go out of range, as my index number will be dropping with the number of open documents.

Thanks, all!

RE: running macro on multiple workbooks

it seems that once you specified a file path, have the macro determine number of excel files in that path.  that would be your counter.  then simply loop through all workbooks, executing the tasks, and then closing the workbooks.  of course, placing these workbooks on one specified path will simplify the task.

if the workbooks are new workbooks with same formatting/features, then perhaps creating one workbook and create new workbooks by copying and naming the file differently.

good luck!
-pmover

RE: running macro on multiple workbooks

Here's a simple macro that illustrates how to access all open workbooks.

Sub getAllOpenWBNames()
  Dim wb As Workbook
  For Each wb In Application.Workbooks
    Debug.Print wb.Name
  Next wb
End Sub

RE: running macro on multiple workbooks

While Not Is Nothing Excel.ActiveWorkbook
   'Do your stuff here
   ActiveWorkbook.close
Wend

RE: running macro on multiple workbooks

(OP)
Thank you all.  I've managed to strongarm Excel and force it to do what I want with the help you've shared.  I appreciate it.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources