PDA

View Full Version : Changing same macro in multiple workbooks



Slow1911s
10-13-2006, 03:54 PM
I have 60 workbooks that have the same print macro and I need to change all of them. Can I do this one time? :banghead:

Bob Phillips
10-13-2006, 04:16 PM
Write some code to go through all the other workbooks, delete that macro, and rewrite it in the new form.

For help in writing to the VBE, see http://www.cpearson.com/excel/vbe.htm

Ken Puls
10-13-2006, 07:24 PM
Better yet, create an add-in with your new print macro, install it, and then cull the code from all 60 workbooks. That way if things ever change on you again, you only have to update the macro once. :)

FYI, if you do go the route that Bob suggested, just be aware that you'll need to have "Trust Access To Visual Basic Project" checked if you are using Excel 2002 or above. (Tools|Macros|Security).

johnske
10-13-2006, 07:34 PM
If this is something that isn't done regularly, it would be much quicker and far simpler to just copy the new procedure or the line you want changed, open each workbook, paste where appropriate, save, and close.

Otherwise - if it's a regular occurrence - there's several ways to approach this programmatically.

If the procedure to be changed has:

1) a different name in each workbook you could write the code to:
open each workbook, then simply do a wildcard search through all the code in the workbook until the line to be replaced was found and replace it.

2) the same name in each workbook you could write the code to:
open each workbook, go through the name of all the procedures until the procedure was found, delete the procedure and write a new one to replace it, or alternatively, just search that procedure for the line to be replaced and replace it.

See VBE Programming (http://www.vbaexpress.com/forum/showthread.php?t=9586) for further help on writing to the VBE window and an example of doing a wildcard search through code. (the example given there is a search and .DeleteLines, you only need to change the .DeleteLines to .ReplaceLine). Here's an example For N = FirstLine To LastLine
If .Lines(N, 1) Like "*email xxx@yyy.com*" Then
.ReplaceLine N, vbTab & "Print #1, " & "email aaa@bbb.net"
End If
Next

Another approach is, as Ken has suggested - an add-in, or, you could look at placing the procedure in a startup workbook e.g. Personal.xls is an example of a startup workbook, so you could simply put it in there (see Startups Vs Add-ins (http://www.vbaexpress.com/forum/showthread.php?t=9747) for more)