Select and Open File (VBA)

[rodrigo_m_almeida]

Good morning,

Could anyone help me with this ?

I would like to adapt this code I am using to select and open only one file instead of the folder...

Public Sub ABC123()
    ' Declare Variables
        Dim xPathName, xFileName As String
    ' Browse File
        Set FileDialog = Application.FileDialog(msoFileDialogFolderPicker)
        FileDialog.Title = "Select Folder"
        If FileDialog.Show = -1 Then
            xPathName = FileDialog.SelectedItems(1)
            Else
                Exit Sub
        End If
        If Right(xPathName, 1) <> "\" Then xPathName = xPathName + "\"
    ' Search Extension
        xFileName = Dir(xPathName & "*.xlsx")
    ' Open File
        Workbooks.Open Filename:=xPathName & xFileName
End Sub

 

[Micron]

you need to use the msofiledialogfilepicker, not the folder picker.
Note also that with multi declarations on a line, you must declare each variable type or else they are variants:
Dim xPathName <--- this is a variant, xFileName As String <--- this is a string
You'll probably raise an error because you have not declared FileDialog. Research the file dialog options to see what you'll need to set for your purpose. Since you'll be picking a file, you shouldn't need If Right(xPathName, 1) to add a slash at the end. Nor should you need
xFileName = Dir(xPathName & "*.xlsx")
or
& xFileName

just
Workbooks.Open Filename:=xPathName

 

[Sektor]

Public Sub SelectFile()
    Dim xPathName, xFileName As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "Select File"
        '// If you need, you can filter files by extension
        With .Filters
            .Clear
            .Add "Excel Files (*.xls*)", "*.xls*" '//xls, xlsx, xlsb xlsm etc.
        End With
        If .Show() Then xFileName = .SelectedItems(1) Else Exit Sub
    End With
    '// Use "xFileName" further
End Sub

 

[Marc L]

Hi, or just using the GetOpenFilename method …​

 

[rodrigo_m_almeida]

you need to use the msofiledialogfilepicker, not the folder picker.
Note also that with multi declarations on a line, you must declare each variable type or else they are variants:
Dim xPathName <--- this is a variant, xFileName As String <--- this is a string
You'll probably raise an error because you have not declared FileDialog. Research the file dialog options to see what you'll need to set for your purpose. Since you'll be picking a file, you shouldn't need If Right(xPathName, 1) to add a slash at the end. Nor should you need
xFileName = Dir(xPathName & "*.xlsx")
or
& xFileName

just
Workbooks.Open Filename:=xPathName

Thanks for explaining to me how it works :D

 

[Micron]

You're welcome. I think at least you learned something about declarations, but probably missed an opportunity to learn more about the dialog and its 13 or so properties and its methods.