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

Use a shortcut key that doesn't include Shift.

--
Regards,
Tom Ogilvy

"Jerry H" wrote in message
...
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?

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


Jerry H wrote:

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?

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

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.



Jerry H wrote:

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