GetOpenFileName Returns Run-time error 91

[goss]

Hi all,

Using Ecxel 2010

I Googled and found some code for GetOpenFileName from Bill Jelen et al
I cobbled it together
At the moment, it prompts me to select a file which I do, but the a Run-time error 91 is returned (Object variable not set) and the file is not open

I tried adding Set here

wbImport = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _
    Title:="Please select a file")

to

Set wbImport = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _
    Title:="Please select a file")

But that cast Run-time error 424 "Object Required"
What am I doing wrong?
Thanks
g

Full snippet:

Option Explicit
Sub GetFile2()

Dim wb As Workbook
Dim wbImport As Workbook
Dim ws As Worksheet
Dim strPath As String

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Data")
strPath = "C:\Users\dlee\Documents\Financial Analysis\Forecast" '<--Change as needed

ChDir strPath
    
    wbImport = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _
    Title:="Please select a file")
    
    If wbImport = False Then
        ' They pressed Cancel
        MsgBox "Stopping because you did not select a file"
        Exit Sub
    Else
    
    Workbooks.Open Filename:=wbImport
    End If
    
    
Set wb = Nothing
Set ws = Nothing

End Sub

 

[VoG]

Try

Sub GetFile2()

Dim wb As Workbook
Dim wbImport As Variant
Dim ws As Worksheet
Dim strPath As String

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Data")
strPath = "C:\Users\dlee\Documents\Financial Analysis\Forecast" '<--Change as needed

ChDir strPath
    
    wbImport = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _
    Title:="Please select a file")
    
    If wbImport = False Then
        ' They pressed Cancel
        MsgBox "Stopping because you did not select a file"
        Exit Sub
    Else
    
    Workbooks.Open Filename:=wbImport
    End If
    
    
Set wb = Nothing
Set ws = Nothing

End Sub

 

[goss]

Thanks Peter,

That did the job

 

[goss]

Hi,

In addition to opening a file that user chooses, I would like to also:

• 
  Clear old data
  Copy the data from the selected file
  Paste the data into the control workbook
  Close the selected file

It appears since I declared wbImport as variance data type below, that I cannot now Set it as a Workbook so I may use the Workbook methods.

Error is

Run-time error '424':
Object required

Here

        With wbImport
            Set wsNew = .Worksheets("WORData")
            lngRows = wsNew.Range("A65536").End(xlUp).Row
            With wsNew
                Set rngImport = .Range("A2:L" & lngRows)
            End With
        End With

Ideas?
Thanks

Full:

Option Explicit

Sub GetDataFile()
    Dim wbBook As Workbook
    Dim wbImport As Variant
    Dim wsData As Worksheet
    Dim wsNew As Worksheet
    Dim rngData As Range
    Dim rngImport As Range
    Dim rngTarget As Range
    Dim strFormula As String
    Dim strPath As String
    Dim lngRows As Long
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    Set wbBook = ThisWorkbook
    Set wsData = wbBook.Worksheets("WORData")
    strPath = "C:\Data" '<--Change as needed
    lngRows = wsData.Range("A65536").End(xlUp).Row
     
    'Clear old data
        With wsData
            Set rngData = .Range("A2:L" & lngRows)
            rngData.ClearContents
            Set rngTarget = .Range("A2")
        End With
    
    'Change Directory
        ChDir strPath
        
    'Get File To Import
        wbImport = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _
        Title:="Please select a file")
    
        If wbImport = False Then
            ' They pressed Cancel
            MsgBox "Stopping because you did not select a file"
            Exit Sub
        Else
    
            Workbooks.Open Filename:=wbImport
        End If
        
        

        With wbImport
            Set wsNew = .Worksheets("WORData")
            lngRows = wsNew.Range("A65536").End(xlUp).Row
            With wsNew
                Set rngImport = .Range("A2:L" & lngRows)
            End With
        End With
        
        rngImport.Copy rngTarget
            
        wbImport.Activate
        ActiveWorkbook.Close
        wbBook.Save
        

    'Tidy up
        Set wbBook = Nothing
        Set wbImport = Nothing
        Set wsData = Nothing
        Set wsNew = Nothing
        Set rngData = Nothing
        Set rngImport = Nothing
        Set rngTarget = Nothing

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .Calculation = xlCalculationAutomatic
    End With
     
End Sub