Runtime 1004 after prompting for filename but not when filename hard coded.

I'm using VBA in Excel 2013 to do something that shouldn't be hard. I want the user to be able to choose a file. When I open I dialog box to choose the file, and then try to open the file, I get a Runtime 1004 error "Sorry, we could find <filename>. Is it possible it was moved, renamed, or deleted?" However, when I hard code workbooks.open <filename with path> it opens fine. Here is the code to prompt for the file:

Sub GetImportFileName2(spath, UFileName)
Dim Filt As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim Title As String
Dim i As Integer
Dim Msg As String
' Set up list of file filters
Filt = "Text Files (*.txt),*.txt," & _
"Comma Separated Files (*.csv),*.csv," & _
"Excel Files (*.xlsx),*.xlsx," & _
"All Files (*.*),*.*"
' Display *.xlsx by default
FilterIndex = 4
' Set the dialog box caption
Title = "Select a file to open:"
' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title, _
MultiSelect:=True)
' Exit if dialog box canceled
If Not IsArray(FileName) Then
MsgBox "No file was selected."
Exit Sub
End If
' Display full path and name of the files
For i = LBound(FileName) To UBound(FileName)
Msg = Msg & FileName(i) & vbCrLf
Next i
spath = Left(Msg, InStrRev(Msg, "\"))
MsgBox "Path: " & spath
UFileName = Mid(Msg, InStrRev(Msg, "\") + 1)
MsgBox "File: " & UFileName
End Sub

As you can see I have inserted a bunch of MsgBox calls so I can check the path and filename. Everything is fine. Then I do:

Workbooks.Open spath & UFileName

And I get the error (the path and filename shown in the error dialog box as not being found is the correct path and filename, so I'm concatenating fine). However, if I just do:

Workbooks.Open FileName:="path and filename exactly as it shows in the error message"

The file opens fine.

Thanks for listening :-)

Sorry, Norie. I'm not a big forum question asker, obviously. OK:

Please Login or Register  to view this content.

And it is called thusly:

Please Login or Register  to view this content.

The MsgBox calls again show the correct path and filename.

Thanks!

Is the msgbox displaying the path showing it has a backslash at the end?
It could be because a backslash is missing, just a thought.

Please Login or Register  to view this content.

Hi everyone. Thanks for the help. Norie, your easy fix did the trick.

As it turns out, the culprit was extra spaces inserted when I converted the Variant that is returned by Application.GetOpenFilename to a String with this little loop:

Please Login or Register  to view this content.

I copied this code from somewhere and I can't say I understand why one would do it this way, now that I'm looking at it. And why the carriage returns (VbCrLf) is a mystery. Removing the carriage returns also ends up with working code but Norie's simple way is best.

Thanks again.

For completeness: what am I doing when I write UFileName = FileName(1)? What does the (1) do? FileName is a Variant, UFileName is a String. I tried UFileName = CStrg(FileName) but it was unhappy.

FileName is given an array of values by Application.GetOpenFileName
FileName(1) would refer to the first value in there

If more than one file is selected, you can refer to them with FileName(2), FileName(3), etc.

Thanks, millz! That would have been my next issue - opening multiple files.