Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Run for every open workbooks (one by one)
Hi guys,
One question: Do you know how to convert the macro: Sub UglyMacro () Application.Run "FirstFile.xls!SameMacroButStoredInThisSpecificWor kbook" ActiveWindow.ActivateNext Application.Run "SecondFile.xls!SameMacroButStoredInThisSpecificWo rkbook" ActiveWindow.ActivateNext Application.Run "ThirdFile.xls!SameMacroButStoredInThisSpecificWor kbook" ActiveWindow.ActivateNext End Sub into macro that will go like: Sub DraftNiceMacro () For each wbk in Application.Workbooks Application.Run "wbk.SameMacroButStoredInThisSpecificWorkbook" Next wbk End Sub This one is not working for some strange reason... Thanks for your help, Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Run for every open workbooks (one by one)
If the same macro is used in several workbooks maybe you can move it
into an add-in. Just an idea. Maybe you can try For each wb in Application.Workbooks Application.Run wb.Name & ".xls!" & "SameMacro" Next wb I didn't test it but I hope it helps -John Coleman wrote: Hi guys, One question: Do you know how to convert the macro: Sub UglyMacro () Application.Run "FirstFile.xls!SameMacroButStoredInThisSpecificWor kbook" ActiveWindow.ActivateNext Application.Run "SecondFile.xls!SameMacroButStoredInThisSpecificWo rkbook" ActiveWindow.ActivateNext Application.Run "ThirdFile.xls!SameMacroButStoredInThisSpecificWor kbook" ActiveWindow.ActivateNext End Sub into macro that will go like: Sub DraftNiceMacro () For each wbk in Application.Workbooks Application.Run "wbk.SameMacroButStoredInThisSpecificWorkbook" Next wbk End Sub This one is not working for some strange reason... Thanks for your help, Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Run for every open workbooks (one by one)
Hi Dave,
Hi John, Thanks a lot for your answers! Just for feedback: The code provided by Dave was almost perfect, I just needed to add "wbk.Activate", as below: ------------- For Each wbk In Application.Workbooks wbk.Activate Application.Run "'" & wbk.Name & "'!SameMacro" Next i ------------ On the other hand, I was unable to run your code, John. Suppose I'm missing something... Cheers, Mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Run for every open workbooks (one by one)
In general, you don't need to activate the workbook to run the macro.
But depending on what your code does and what workbook/worksheet should be affected, you may even want to activate/select a specific sheet, too. wrote: Hi Dave, Hi John, Thanks a lot for your answers! Just for feedback: The code provided by Dave was almost perfect, I just needed to add "wbk.Activate", as below: ------------- For Each wbk In Application.Workbooks wbk.Activate Application.Run "'" & wbk.Name & "'!SameMacro" Next i ------------ On the other hand, I was unable to run your code, John. Suppose I'm missing something... Cheers, Mark -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Run for every open workbooks (one by one)
wb.name would include "xls", so you don't need to add that.
-- Regards, Tom Ogilvy "John Coleman" wrote in message ups.com... If the same macro is used in several workbooks maybe you can move it into an add-in. Just an idea. Maybe you can try For each wb in Application.Workbooks Application.Run wb.Name & ".xls!" & "SameMacro" Next wb I didn't test it but I hope it helps -John Coleman wrote: Hi guys, One question: Do you know how to convert the macro: Sub UglyMacro () Application.Run "FirstFile.xls!SameMacroButStoredInThisSpecificWor kbook" ActiveWindow.ActivateNext Application.Run "SecondFile.xls!SameMacroButStoredInThisSpecificWo rkbook" ActiveWindow.ActivateNext Application.Run "ThirdFile.xls!SameMacroButStoredInThisSpecificWor kbook" ActiveWindow.ActivateNext End Sub into macro that will go like: Sub DraftNiceMacro () For each wbk in Application.Workbooks Application.Run "wbk.SameMacroButStoredInThisSpecificWorkbook" Next wbk End Sub This one is not working for some strange reason... Thanks for your help, Mark |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Run for every open workbooks (one by one)
Thanks Tom
I see what happened. I wasn't sure about that point off the top of my head so before my post I opened up Excel and wrote a quick sub to run MsgBox ActiveWorkbook.Name and saw "Book 1" displayed - but I hadn't *saved* it yet and the .xls is only added upon the first save. Thanks again -John Coleman Tom Ogilvy wrote: wb.name would include "xls", so you don't need to add that. -- Regards, Tom Ogilvy "John Coleman" wrote in message ups.com... If the same macro is used in several workbooks maybe you can move it into an add-in. Just an idea. Maybe you can try For each wb in Application.Workbooks Application.Run wb.Name & ".xls!" & "SameMacro" Next wb I didn't test it but I hope it helps -John Coleman wrote: Hi guys, One question: Do you know how to convert the macro: Sub UglyMacro () Application.Run "FirstFile.xls!SameMacroButStoredInThisSpecificWor kbook" ActiveWindow.ActivateNext Application.Run "SecondFile.xls!SameMacroButStoredInThisSpecificWo rkbook" ActiveWindow.ActivateNext Application.Run "ThirdFile.xls!SameMacroButStoredInThisSpecificWor kbook" ActiveWindow.ActivateNext End Sub into macro that will go like: Sub DraftNiceMacro () For each wbk in Application.Workbooks Application.Run "wbk.SameMacroButStoredInThisSpecificWorkbook" Next wbk End Sub This one is not working for some strange reason... Thanks for your help, Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreadsheet will not open without Excel Application being open fir | Excel Discussion (Misc queries) | |||
Can Excel open a new application every time you open a file? | Setting up and Configuration of Excel | |||
Can not open excel files without open application | Excel Discussion (Misc queries) | |||
Using workbooks.open after new instance of excel application | Excel Programming | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming |