Open existing file

[Guest]

I am trying to open existing files in Excel. I am using the GetOpenFilename
method to select the files.

Sub SelectBookToOpen()

Dim FileToOpen As Variant
Dim MyPath As String
Dim Wb As Workbook

MyPath = "T:\Archive"
ChDrive "T"
ChDir MyPath

FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , _
"Please select both previous and current weeks' Data Archives", , True)

If FileToOpen <> False Then
Set Wb = Workbooks.Open(FileToOpen)
Else
Exit Sub
End If

End Sub

Everything works fine until I actually select a file and click Open. Then I
get a data mismatch error on this line: If FileToOpen <> False Then

What am I doing wrong?
Thanks, Lee

 

[PO]

Hi FinChase

If the user doesn't select a file, GetOpenFilename returns False (Boolean
value). If the user however selects one or more files GetOpenFilename
returns an array of variants.

Try this:

Sub SelectBookToOpen()

Dim FileToOpen As Variant
Dim MyPath As String
Dim Wb As Workbook
Dim i As Integer

MyPath = "T:\Archive"
ChDrive "T"
ChDir MyPath

FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls", ,
_
"Please select both previous and current weeks' Data Archives", , True)

If VarType(FileToOpen) <> vbBoolean Then
For i = 1 To UBound(FileToOpen)
Set Wb = Workbooks.Open(FileToOpen(i))
Next
Else
'User canceled
Exit Sub
End If

End Sub

 

[Guest]

That worked! Thanks for your help!

Hi FinChase

If the user doesn't select a file, GetOpenFilename returns False (Boolean
value). If the user however selects one or more files GetOpenFilename
returns an array of variants.

Try this:

Sub SelectBookToOpen()

Dim FileToOpen As Variant
Dim MyPath As String
Dim Wb As Workbook
Dim i As Integer

MyPath = "T:\Archive"
ChDrive "T"
ChDir MyPath

FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls", ,
_
"Please select both previous and current weeks' Data Archives", , True)

If VarType(FileToOpen) <> vbBoolean Then
For i = 1 To UBound(FileToOpen)
Set Wb = Workbooks.Open(FileToOpen(i))
Next
Else
'User canceled
Exit Sub
End If

End Sub