Assigning variable to open workbook

**DavidBW** · 07-19-2011, 12:37 PM

Hello

I want to create a report that pulls in data from a shared workbook containing multiple worksheets.
Unfortunately I'm stumbling fairly early as I can't set the variable to refer back to the original file containing the data once it has been opened. Basically I need to be able to toggle between two workbooks but the name of one will vary every time it is used to create the report. Code is as follows:

Sub ReportMacro()
Dim fNameAndPath As Variant
Dim THISWB As Workbook

'Step 1

fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
Workbooks.Open Filename:=fNameAndPath

Set THISWB = ActiveWorkbook

On Error Resume Next


Dim WB As Workbook
WB = "FSD BAS report test.xls"

'
    Windows(THISWB).Activate
    Sheets("Core Input").Select
    Range("I8").Select
    Application.CutCopyMode = False
    Selection.Copy
   
    Windows(WB).Activate
    Range("A3").Select
   
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Windows(THISWB).Select

Works OK to this point but doesn't go back to file opened at step 1 above but stays in current as this is now deemed to be the active workbook

Thanks in anticipation

**tigeravatar** · 07-19-2011, 02:52 PM

DavidBW,

In the future, please wrap your code in code tags. As to your question, you need to set a variable before opening the workbook, and then set the new workbook to its own variable. Here's an example:

Sub AssigningWorkbooksToVariablesExample()
    
    Dim wb2Path As String: wb2Path = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
    If wb2Path = "False" Then Exit Sub
    
    Dim wb1 As Workbook: Set wb1 = ActiveWorkbook
    Dim wb2 As Workbook: Set wb2 = Workbooks.Open(wb2Path)
    
    MsgBox "1st workbook: " & wb1.Name & Chr(10) & "2nd workbook: " & wb2.Name
    
End Sub

Hope that helps,
~tigeravatar

**DavidBW** · 07-21-2011, 05:00 AM

thanks, that bit works but now I get a "Run-time error 13 - Type mismatch" error when trying to activate the wb1 or wb2 workbooks.

**romperstomper** · 07-21-2011, 05:55 AM

You don't use:

Windows(THISWB).Activate

for example as THISWB is a workbook object, not the name of a workbook. It's just:

THISWB.Activate

It is however almost never necessary to actually activate or select anything in code.

**DavidBW** · 07-21-2011, 05:56 AM

sorry, just to clarify what I would like to do in this macro and that is to simply toggle between wb1 and wb2 (I will be copying data from wb2 into wb1)

thanks

**romperstomper** · 07-21-2011, 06:04 AM

Based on your posted sample:


Sub ReportMacro()
   Dim fNameAndPath As Variant
   Dim THISWB As Workbook
   Dim ThatWB As Workbook
   
   'Step 1
   ' store a reference to the current active workbook
   Set THISWB = ActiveWorkbook
   
   fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
   If fNameAndPath = False Then Exit Sub
   
   ' store reference to opened workbook
   Set ThatWB = Workbooks.Open(Filename:=fNameAndPath)
   
   On Error Resume Next

   'cop from opened workbook
    ThatWB.Sheets("Core Input").Range("I8").Copy
    ' paste in original workbook
    
    ' NOTE: no need to activate or select anything!
    
   THISWB.ActiveSheet.Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False