Open existing file

F

FinChase

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
 
P

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
 
F

FinChase

That worked! Thanks for your help!

PO said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top