Select and Open File (VBA)

rodrigo_m_almeida

New Member
Joined
Jan 13, 2022
Messages
42
Office Version
  1. 2021
Platform
  1. Windows
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...

VBA Code:
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
VBA Code:
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
 
Upvote 0
Solution
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top