Referening VBA GetOpenFilename (Excel Macro)

Question

I'm running an Excel Macro that is store in the active workbook. I want it to prompt to open a file, referenced as OldWorkbook and copy values from OldWorkbook then paste their values into the active workbook.

I'm having errors after the 'Copy range to clipboard note. It's like it doesn't recognize any value to "OldWorkbook" at this point?

Sub Version_Convert()

Dim OldWorkbook As Variant

OldWorkbook = Application.GetOpenFilename("Excel Files (*.xl*),*.xl*", , "Choose File", "Open", False)
If OldWorkbook = "False" Then

Else
Workbooks.Open (OldWorkbook)
End If

'Copy range to clipboard
Workbooks(OldWorkbook).Worksheets("PKG").Range("B12:CW28").Copy

'PasteSpecial to paste values, formulas, formats, etc.
ThisWorkbook.Worksheets("PKG").Range("B12:CW28").PasteSpecial Paste:=xlPasteValues
  
End Sub

Why is using the string giving me an error? How do I make OldWorkbook usable?

Answer 1

Referencing and Copying

• Application.GetOpenFilename will return either the (full) path of a file (String) or False (Boolean). That's why you have to declare OldWorkbook as a Variant in the first place. Therefore it is incorrect to use the quotes ("False").
• Let's say the result (file path) of Application.GetOpenFilename is C:\Test\Test.xlsx.
  In your code, you are actually trying to do
  Workbooks("C:\Test\Test.xlsx").Worksheets("PKG").Range("B12:CW28").Copy (wrong) instead of
  Workbooks("Test.xlsx").Worksheets("PKG").Range("B12:CW28").Copy (correct), because Workbooks(...) needs the file name, while Workbooks.Open(...) needs the file path.
• The best way to deal with this is by implementing a workbook variable, as BigBen suggested in the comments, i.e. by creating a reference to the workbook when opening it. Then you don't have to care about file names and file paths and you just use the variable in the continuation of the code.
• When copying values, copying by assignment is more efficient than using PasteSpecial (see solutions 2 and 3).

Option Explicit


Sub VersionConvertQuickFix()

    Dim wb As Workbook
    Dim OldWorkbook As Variant
    
    OldWorkbook = Application.GetOpenFilename("Excel Files (*.xl*),*.xl*", , "Choose File", "Open", False)
    If OldWorkbook = False Then
        Exit Sub ' To prevent an error from occurring when canceling the dialog.
    Else
        Set wb = Workbooks.Open(OldWorkbook)
    End If
    
    'Copy range to clipboard
    wb.Worksheets("PKG").Range("B12:CW28").Copy
    
    'PasteSpecial to paste values, formulas, formats, etc.
    ThisWorkbook.Worksheets("PKG").Range("B12:CW28").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
  
End Sub


Sub VersionConvert()
    
    ' Create a reference to the Source Range.
    Dim swbPath As Variant
    swbPath = Application.GetOpenFilename( _
        "Excel Files (*.xl*),*.xl*", , "Choose File", "Open", False)
    If swbPath = False Then
        'MsgBox "Canceled."
        Exit Sub
    End If
    Dim swb As Workbook: Set swb = Workbooks.Open(swbPath)
    Dim srg  As Range: Set srg = swb.Worksheets("PKG").Range("B12:CW28")
    
    ' Create a reference to the Destination Range.
    Dim drg As Range: Set drg = ThisWorkbook.Worksheets("PKG").Range("B12:CW28")
        
    ' Copy by assignment (values only).
    drg.Value = srg.Value
    
    'swb.Close SaveChanges:=False
    'drg.Worksheet.Parent.Save ' or ThisWorkbook.Save

End Sub


Sub VersionConvertDifferentDestinationAddress()
    
    ' Create a reference to the Source Range.
    Dim swbPath As Variant
    swbPath = Application.GetOpenFilename( _
        "Excel Files (*.xl*),*.xl*", , "Choose File", "Open", False)
    If swbPath = False Then
        'MsgBox "Canceled."
        Exit Sub
    End If
    Dim swb As Workbook: Set swb = Workbooks.Open(swbPath)
    Dim sws As Worksheet: Set sws = swb.Worksheets("PKG")
    Dim srg As Range: Set srg = sws.Range("B12:CW28")
    
    ' Create a reference to the Destination Range.
    Dim dwb As Workbook: Set dwb = ThisWorkbook
    Dim dws As Worksheet: Set dws = dwb.Worksheets("PKG")
    Dim dfCell As Range: Set dfCell = dws.Range("A1") ' e.g.
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
    
    ' Copy by assignment (values only).
    drg.Value = srg.Value

    'swb.Close SaveChanges:=False
    'dwb.Save
    
End Sub