|
|
Open two new workbooks with macro |
|
Author |
Message |
lilismiles
|
Posted: Sat May 01 18:35:35 CDT 2004 |
Top |
Excel Programming >> Open two new workbooks with macro
I want to automate opening multiple workbooks.
For testing purposes, I wrote a simple macro entered with pgm.xls, which is simply
Sub macro1(
Workbooks.Open Filename:="D:\My Documents\jeri\Book1.xls
MsgBox "continue
Workbooks.Open Filename:="D:\My Documents\jeri\Book2.xls
End Su
I assigned the above macro1 with the keyboard shortcut key Control-Shift-T. The macro runs fine if I select to run it
"manually" (under menu bar, select macro, and so on.) But if I try to run the above macro with the keyboard shortcut, it only open book1.xls and stops
I want to make life simpler by using keyboard shortcut. What should I do to make it work? Do I need to build a custom toolbar or what
Excel37
|
|
|
|
|
Tom
|
Posted: Sat May 01 18:35:35 CDT 2004 |
Top |
Excel Programming >> Open two new workbooks with macro
Use a shortcut key that doesn't include Shift.
--
Regards,
Tom Ogilvy
> I want to automate opening multiple workbooks.
>
> For testing purposes, I wrote a simple macro entered with pgm.xls, which
is simply:
>
> Sub macro1()
> Workbooks.Open Filename:="D:\My Documents\jeri\Book1.xls"
> MsgBox "continue"
> Workbooks.Open Filename:="D:\My Documents\jeri\Book2.xls"
> End Sub
>
> I assigned the above macro1 with the keyboard shortcut key
Control-Shift-T. The macro runs fine if I select to run it
> "manually" (under menu bar, select macro, and so on.) But if I try to run
the above macro with the keyboard shortcut, it only open book1.xls and
stops.
>
> I want to make life simpler by using keyboard shortcut. What should I do
to make it work? Do I need to build a custom toolbar or what?
>
|
|
|
|
|
yogendra
|
Posted: Sun May 02 00:54:30 CDT 2004 |
Top |
Excel Programming >> Open two new workbooks with macro
Why don't you try Workspace in Excel?
Open all the files, immaterial where they reside, then
File -> Save As Workspace... and name it something....
When you want to open all the files, just open the workspace....
Hope this helps.... also reduces your code....
You can also open this workspace from a code... if you insist...
instead of opening each file separately.
Also see
http://j-walk.com/ss/excel/usertips/tip029.htm
Thanks,
Yogendra
> I want to automate opening multiple workbooks.
>
> For testing purposes, I wrote a simple macro entered with pgm.xls, which is simply:
>
> Sub macro1()
> Workbooks.Open Filename:="D:\My Documents\jeri\Book1.xls"
> MsgBox "continue"
> Workbooks.Open Filename:="D:\My Documents\jeri\Book2.xls"
> End Sub
>
> I assigned the above macro1 with the keyboard shortcut key Control-Shift-T. The macro runs fine if I select to run it
> "manually" (under menu bar, select macro, and so on.) But if I try to run the above macro with the keyboard shortcut, it only open book1.xls and stops.
>
> I want to make life simpler by using keyboard shortcut. What should I do to make it work? Do I need to build a custom toolbar or what?
>
|
|
|
|
|
anonymous
|
Posted: Sun May 02 08:56:04 CDT 2004 |
Top |
Excel Programming >> Open two new workbooks with macro
Thanks for introducing workspace concept, Yogendra. However, the files need to be opened were not static. In fact I need to create other scripts in VBA so that it displays all valid files in a spreadsheet based on certain conditions. Then I allow the user to select one or more files for opening
While Tom's suggestion does work in this case, I wonder why this was the behavior of Excel. Is it a Excel bug or what
Jerr
|
|
|
|
|
Dave
|
Posted: Sun May 02 15:12:50 CDT 2004 |
Top |
Excel Programming >> Open two new workbooks with macro
I'd classify it as an over zealous feature <bg>.
When you open a workbook while holding the shift key, the auto_open and
workbook_open procedures won't fire.
And the shift in the shortcut key combination confuses excel enough to say:
Stop running the code.
>
> Thanks for introducing workspace concept, Yogendra. However, the files need to be opened were not static. In fact I need to create other scripts in VBA so that it displays all valid files in a spreadsheet based on certain conditions. Then I allow the user to select one or more files for opening.
>
> While Tom's suggestion does work in this case, I wonder why this was the behavior of Excel. Is it a Excel bug or what?
>
> Jerry
--
Dave Peterson
|
|
|
|
|
|
|