application.getopenfilename if cancel is clicked?

[dfida]

I've seen several threads on this, none of which worked for me...mines seems simple?

I want to code below to prompt the user for a file, using application.getopenfilename. When the file is open, it copies a range, then pastes it to ThisWorkBook.

If the user clicks "cancel" I want to macro to end.

So....the "cancel" part works fine (i.e. goes to Exit Sub), but when I actually click on a file, I get a error 13, type mismatch, and the line " If varDave = False Then" is highlighted when I debug.

Any ideas? I'm sure I'm missing something very simple...

Sub testOpenFile()

Dim varDave As String

varDave = Application.GetOpenFilename("Excel Files (*.xlsx), *.xls)")

If varDave = False Then
MsgBox "No file selected. Cannot continue."
Exit Sub

Else

Workbooks.Open varDave
varDave = ActiveWorkbook.Name
Sheets("sheet1").Range("a1:a20").Copy

ThisWorkbook.Activate
Sheets("sheet1").Select
Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Windows(varDave).Activate
Windows(varDave).Close

End If

End Sub

 

[ZVI]

Use: Dim varDave As Variant

 

[ZVI]

Your code tweaked a bit:

Sub testOpenFile()

  Dim varDave
  
  varDave = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
  
  If varDave = False Then
    MsgBox "No file selected. Cannot continue."
    Exit Sub
  End If

  On Error GoTo exit_
  Application.ScreenUpdating = False
  
  With Workbooks.Open(varDave)
    .Sheets("sheet1").Range("a1:a20").Copy
    ThisWorkbook.Sheets("sheet1").Range("a1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    .Close False
  End With
  ThisWorkbook.Activate
  
exit_:
  
  Application.ScreenUpdating = True
  If Err Then MsgBox Err.Description, vbCritical, "Error"

End Sub

 

[dfida]

Use: Dim varDave As Variant

That worked perfectly...THANKS!

 

[dfida]

Very nice...thanks again! I have a lot to learn..

Your code tweaked a bit:

Sub testOpenFile()

  Dim varDave
  
  varDave = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*")
  
  If varDave = False Then
    MsgBox "No file selected. Cannot continue."
    Exit Sub
  End If

  On Error GoTo exit_
  Application.ScreenUpdating = False
  
  With Workbooks.Open(varDave)
    .Sheets("sheet1").Range("a1:a20").Copy
    ThisWorkbook.Sheets("sheet1").Range("a1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    .Close False
  End With
  ThisWorkbook.Activate
  
exit_:
  
  Application.ScreenUpdating = True
  If Err Then MsgBox Err.Description, vbCritical, "Error"

End Sub

 

[ZVI]

Glad to help, dfida! Thank you for the feedback.