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:

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

And it is called thusly:

Sub OpenGemsFile()
'
' Prompt for file and open
'

'
Dim Msg As String
    Dim UFileName As String
    Dim spath As String
    ChDir ("c:\")
    Call GetImportFileName2(spath, UFileName)
    MsgBox spath
    MsgBox UFileName
    Workbooks.Open spath & UFileName
' Workbooks.Open FileName:="C:\Users\xxxxxx\Documents\Ramp Up Study\Ramp Up Study.xlsx"
End Sub

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.

Workbooks.Open spath & "\" & UFileName

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:

 For i = LBound(FileName) To UBound(FileName)
        Msg = Msg & FileName(i) & VbCrLf
    Next i

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.