Opening multiple workbook via a master workbook

dbmccallum

New Member
Joined
Nov 7, 2017
Messages
6
Hi All,

I don't know what I am doing wrong

I have a master workbook that opens a new template file (workbook 1) and a data file (workbook 2).

The code below works in certain cases but not all.

Both workbooks closed prior to executing the vba - PASS
Both workbooks open prior to executing the vba - FAIL (both wb names become wb2's)
First workbook open second closed - PASS
First workbook closed second open - FAIL (both wb names become wb1's)

Code:
Public xlscope As Workbook


Sub Open_Two_Workbooks()
'
Dim vWB1 As Variant
Dim wbWB1 As Workbook
Dim vWB2 As Variant
Dim wbWB2 As Workbook


Set xlscope = ThisWorkbook
SourcePath = xlscope.Path & ""


'--------------------------------------------------------------------
'Open First File
'--------------------------------------------------------------------
vWB1 = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Workbook 1", "Open", False)
' If Cancel then Exit
If vWB1 = "False" Then
' do nothing
GoTo endjmp
Else
Set wbWB1 = Workbooks.Open(vWB1, False)
tTemplateName = wbWB1.Name
End If
'--------------------------------------------------------------------
'Open Second File
'--------------------------------------------------------------------
vWB2 = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Workbook 2", "Open", False)
'If Cancel then Exit
If vWB2 = "False" Then
' do nothing
GoTo endjmp
Else
Set wbWB2 = Workbooks.Open(vWB2, False)
' check the files
result = MsgBox("Importing a Workbooks" & vbCr & _
"Variant 1 is " & vWB1 & vbCr & _
"Variant 2 is " & vWB2 & vbCr & _
"Workbook 1 is " & tTemplateName & vbCr & _
"Workbook 2 is " & wbWB2.Name, vbOKCancel)
If result = 2 Then
wbWB1.Close SaveChanges:=False
wbWB2.Close SaveChanges:=False
GoTo endjmp
End If
End If
' I do a bunch of manipulations here between the master workbook and the two newly opened


endjmp:


End Sub
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Watch MrExcel Video

Forum statistics

Threads
1,095,750
Messages
5,446,271
Members
405,393
Latest member
sully361

This Week's Hot Topics

Top