VBA

[Rawan_7]

Hello, I need assistance with VBA. How to Open the dialog box and gets a file name from the user without actually opening any files.

 

[Micron]

msoFileDialogFilePicker will return file name but not open it (unless you add code to do that). Add the Sub name and End Sub lines you need.

Dim fd

Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
   .Title = "Select File"
   .AllowMultiSelect = False
   .InitialFileName = "C:\Users\Micron\Documents\Excel\A*"
   .Filters.Add "EXCEL", "*.xlsm", 1
End With
If fd.Show = -1 Then Workbooks.Open fd.SelectedItems(1) 'alter this line to not open the file

Set fd = Nothing

If you don't want users to be able to nmulti select, leave that as False else you will have to alter code to loop over selections.
If you don't want to provide the starting point, remove the InitialFileName line. Filters property should seem obvious to you.
You should have If fd.Show = -1 Then (-1 means a selection was made) so that you can handle the situation where the user cancels. You need a reference to Microsoft Office if I recall correctly.

 

[Jaafar Tribak]

Look up the GetOpenFilename Method

Sub example()
    Dim sFileName As String
   
    sFileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xlsx")
    If sFileName <> "False" Then
       MsgBox "You chose : '" & sFileName & "'"
    Else
       MsgBox "You cancelled the operation."
    End If
End Sub