running macro on multiple workbooks

[jistre]

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!

 

[ivymike]

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

 

[jistre]

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

 

[francesca]

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!)

 

[jistre]

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.

 

[jistre]

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!

 

[pmover]

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

 

[cummings54]

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

 

[handleman]

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

 

[jistre]

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.