Opening 2 workbooks simultaneously with Macros

D

daniel chen

ABC.xls and DEF.xls are 2 workbooks in my directory - C:\Temp
In my desktop workbook, Column A serve as the index list.
I entered the following :
Cell A1 =ABC
Cell A2 =DEF
I have no problem with Macro1, Macro2, and Macro4.
Macro3 wouldn't work as expected - open both workbooks at the same time like
Macro4.
Why is that? Please help.

Sub Macro1()
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & Cells(1, 1) & ".xls")
End Sub

Sub Macro2()
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & Cells(2, 1) & ".xls")
End Sub

Sub Macro3()
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & Cells(1, 1) & ".xls")
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & Cells(2, 1) & ".xls")
End Sub

Sub Macro4()
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\ABC.xls")
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\DEF.xls")
End Sub
 
J

JE McGimpsey

Cells defaults to the activesheet so

Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & Cells(1, 1) & ".xls")
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & Cells(2, 1) & ".xls")

is equivalent to

Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & ActiveSheet.Cells(1, 1) & ".xls")
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & ActiveSheet.Cells(2, 1) & ".xls")

but after you open the first workbook, that workbook's sheet is active,
not your original one.

Try:

Public Sub Macro3()
Const sPATH As String = "C:\Temp\"
Const sXTN As String = ".xls"
Dim Wkbk As Workbook
Dim sFileName1 As String
Dim sFileName2 As String

sFileName1 = sPATH & Cells(1, 1) & sXTN
sFileName2 = sPATH & Cells(2, 1) & sXTN
Workbooks.Open sFileName1
Set Wkbk = Workbooks.Open sFileName2
End Sub
 
D

Dave Peterson

When you open a new workbook, that becomes the activeworkbook and the
activesheet is now in that newly opened workbook:

Sub Macro3()
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & Cells(1, 1) & ".xls")
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & Cells(2, 1) & ".xls")
End Sub

Since you didn't qualify cells(), it refers to the activesheet.

Maybe:

Sub Macro3()

with activesheet
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & .Cells(1, 1).value & ".xls")
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & .Cells(2, 1).value & ".xls")
end with

End Sub

notice the dots and the with/end with statements. (I like specify the property,
too (.value), but it isn't necessary.)

(I didn't test this, though.)
 
D

daniel chen

Hi, JE
Well explained! Thank you very much.

JE McGimpsey said:
Cells defaults to the activesheet so

Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & Cells(1, 1) & ".xls")
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & Cells(2, 1) & ".xls")

is equivalent to

Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & ActiveSheet.Cells(1, 1) & ".xls")
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & ActiveSheet.Cells(2, 1) & ".xls")

but after you open the first workbook, that workbook's sheet is active,
not your original one.

Try:

Public Sub Macro3()
Const sPATH As String = "C:\Temp\"
Const sXTN As String = ".xls"
Dim Wkbk As Workbook
Dim sFileName1 As String
Dim sFileName2 As String

sFileName1 = sPATH & Cells(1, 1) & sXTN
sFileName2 = sPATH & Cells(2, 1) & sXTN
Workbooks.Open sFileName1
Set Wkbk = Workbooks.Open sFileName2
End Sub
Click to expand...
 
D

daniel chen

Hi, Dave
Understanding JE McGimpsey's explanation, I tried the following by
re-activating the Sheet - and it worked.
I definitely like yours. Thank you again.
Sub Macro3()
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & Cells(1, 1) & ".xls")
Windows("Test.xls").Activate
Sheets("Index").Select
Set Wkbk = Workbooks.Open _
(Filename:="C:\Temp\" & Cells(2, 1) & ".xls")
End Sub
 
D

Dave Peterson

One of the benefits of J.E.'s code is that it's a little more easy to update if
the path changes. You only have to change it once. This makes the code easier
to copy from one project to another. You adjust these two lines:

Const sPATH As String = "C:\Temp\"
Const sXTN As String = ".xls"

to match the current configuration and you're off and running.

With my suggestion, you avoid the activates and selects, but you have to be a
little more careful if things change.
 
D

daniel chen

Hi Dave,
What significant differences between
Public Sub Macro3()
and Sub Macro3()
I have another question on the subject -path changes.
Using DOS's Subst function, a virtual drive letter can be assigned to
replace a path and can be used in Excel.
However, any formula associated with that drive letter will be lost, when
the drive letter is deleted - it won't revert itself to its orginal path.
Is there a way to assign a letter or word to replace a frequent used path
(in formula) that applied to Excel as-a-whole temporarily and will revert
to its original path.
 
D

Dave Peterson

Under normal circumstances, there's no difference between

Public Sub macro3()
and
Sub macro3()

The real difference occurs with
private sub macro3()

This sub won't be seen from other modules or from excel's tools|macros|macro...
list.

======

You use:
Subst e: "c:\my documents\excel\test\testing\workstuff"
(to save typing?)

And then later
subst e: /d
to remove the substitution.

====
I can think of two ways that might help--depending on the length of your
formulas.

Edit|Links|change links
(Probably easiest)

or
Edit|Replace
e:
with the real path.

The bad news is you have to watch out for the lengths of those formulas. Excel
has a limit of 1024 characters per formula (when in R1C1 mode).

I would think that you're using Subst to keep the formula's length shorter.

This might not help in all situations, but maybe you could check to see if that
drive letter exists and if not, run Subst.

The bad news is if someone else is using that drive letter for a different
drive, then you may either screw them up or still not get the right workbooks.

This just checks to see if that drive letter exists--if it doesn't it shells to
DOS to run Subst.

Option Explicit
Sub auto_open()

Dim TestStr As String

TestStr = ""
On Error Resume Next
TestStr = Dir("E:\nul")
On Error GoTo 0

If TestStr = "" Then
Shell Environ("comspec") & " /c subst e: " _
& Chr(34) & "c:\my documents\excel" & Chr(34), vbHide
End If

End Sub

===========
One more option.

I bet you'd really like to use =indirect() in your formula to put the path in a
single cell and use that cell to build your formula.

The bad news is that =indirect() won't work with a closed workbook.

But Harlan Grove posted a function that retrieves the value from a separate
instance of excel:
http://google.com/[email protected]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




daniel said:
Hi Dave,
What significant differences between
Public Sub Macro3()
and Sub Macro3()
I have another question on the subject -path changes.
Using DOS's Subst function, a virtual drive letter can be assigned to
replace a path and can be used in Excel.
However, any formula associated with that drive letter will be lost, when
the drive letter is deleted - it won't revert itself to its orginal path.
Is there a way to assign a letter or word to replace a frequent used path
(in formula) that applied to Excel as-a-whole temporarily and will revert
to its original path.
Click to expand...
 
D

daniel chen

You scripted the subst e: macro. How would you script an subst e: /d macro.
Any suggestion on books for new beginner. Thanks.
 
D

Dave Peterson

I'd change this:

Shell Environ("comspec") & " /c subst e: " _
& Chr(34) & "c:\my documents\excel" & Chr(34), vbHide

to:

Shell Environ("comspec") & " /c subst e: /d "


But a word of warning. In my light testing (win98), if I were using the E:
drive at a DOS prompt, then I didn't remove the substitution. But if I were in
Windows Explorer in a folder on that E: drive, it got removed.



daniel said:
You scripted the subst e: macro. How would you script an subst e: /d macro.
Any suggestion on books for new beginner. Thanks.
Click to expand...
 
D

daniel chen

Finally, I'd it right. Here is the final shape.

Sub auto_close()
On Error Resume Next
TestStr = Dir("X:\nul")
On Error GoTo 0
If TestStr = "nul" Then
Shell Environ("comspec") & " /c subst x: /d "
End If
End Sub

On the safe side, I use x: in place of e:
Thanks
 
D

Dave Peterson

That sounds backwards to me.

dir("x:\nul") will return NUL if that drive letter exists. Then you use Subst
again.

And watchout for undeclared variables (Dim's are your friend).

And watchout for upper/lower case problems. nul <> NUL.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can anybody help on macro please? 8
Opening a xl workbook using wildcard 2
HELP - coding on Macro! 3
Updating workbook from protected workbooks... 1
Combine worksheets in multiple workbook in one workbook with a macro 2
Passing variables between excel files 2
Open four Wbooks, copy four columns from each to Master Wbook 16
Get cell value from other workbook 3

Top