Astra

VBA Prompt for File Location

Estimated reading: 2 minutes

In this tutorial, we will learn to prompt the user for the file or folder location using the VBA code.

The following code will open the folder or file if the path is provided in the input box

Sub Open_File_Location()
    
    Dim file_Path As String
    
    'Prompt user for file location
    'assign the path entered in the inputbox to the  file_Path variable
    file_Path = InputBox("Please enter the file or folder location:")

      'Check if user entered a file location
    If file_Path <> "" Then
        
        'Do something with the file location
       Call Shell("cmd /c start " & file_Path, vbNormalFocus)
    
    Else
        
        'User cancelled the input
        MsgBox "No file or folder location was entered."
    
    End If
    
End Sub

This code should open the specified file using the default program associated with the file type. If the file is a spreadsheet, for example, it will be opened with Excel

The following code allows the user to pick a file  and pass the path to the cell D17

Sub Select_File_Or_Folder()
    
    Dim file_Path As fileDialog
    
    'creates a FileDialog object and assigns it to the file_Path variable
    'The msoFileDialogFilePicker argument specifies the type of file dialog to display
    Set file_Path = Application.fileDialog(msoFileDialogFilePicker)
    
    'disables user to Multi Select
    file_Path.AllowMultiSelect = False
    
    'Title of the file picker dilogue box
    file_Path.Title = "Select File"
    
    file_Path.Filters.Clear
    'filter allows user to seclects all types of files
    file_Path.Filters.Add "All Files", "*.*"
   
    'Display the file dialog box
    If file_Path.Show = -1 Then
        
        MsgBox "You selected file: " & file_Path.SelectedItems(1)

        'pass the path of the selcected file to the cell D17
        Range("D17").Value = "You selected: " & file_Path.SelectedItems(1)
    Else
        'User cancelled the selection
        MsgBox "No selection was made."
    
    End If
        

End Sub

Note: Please check the references to run the code

VBA Prompt for File Location

1 thought on “VBA Prompt for File Location”

Leave a Reply

Your email address will not be published. Required fields are marked *

Share this Doc
Jump to Topics
SOLVED EXCEL
Scroll to Top