Windows FileDialog to Select File or Folder Path in VBA

.

Dear friends,

In my many Excel Tools, wherever there is a need for a file path to be entered from your local PC, I try to put a browse button to locate that file in your Windows PC through the windows file explorer window. It is the same window that you might have seen in windows for selecting a file.

File Dialog - File Picker

File Dialog – File Picker

How to create your own button in a style whatever you like

You can say what is a big deal in it to create a button in Excel. Anyone can add a button from developer tab. Yes, I agree but in this button, you may not find options to give a lot of effect and style you want to give it to your button. So to do that here is a simple trick. In Microsoft Office PowerPoint on even in your excel sheet, you can design your own button however you like. Here are some samples, I have designed for you. Download it and start making your macro buttons look like any stylish webpage buttons.
Now, though I have been mentioning above that you can create different types of Stylish buttons, actually, I lied to you. What I mean is you can create an image/shape which will look like a button. But don’t worry, in the next step you will see that it will also work like a button 🙂

Design Buttons in Excel

Design Buttons in Excel

How to assign a macro to an Image button

Unlike a command button in excel you just can not double click and it will take you to the VBA code editor where you can write a code that you want to be executed on clicking on those images or buttons whatever you call them 🙂
To work like a button you need to create a Sub procedure i.e. in other words macro which you want to execute on clicking this image or shapes.

Now right-click on the image/shape which you have added and click on Assign Macro as shown in the below image

How to assign Macro to an Image

How to assign Macro to an Image

Code to make a browse button work

FileDialog is the object which is used for windows file explorer. There are 4 different types of dialogs which you can choose as shown below. Here we need to get the path of a file then we will use dialog type as msoFileDialogFilePicker

File Dialog in VBA

File Dialog in VBA

VBA to Select a File Path using Windows File Dialog


Sub browseFilePath()
    On Error GoTo err
    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)
    
    'To allow or disable to multi select
    fileExplorer.AllowMultiSelect = False
    
    With fileExplorer
        If .Show = -1 Then 'Any file is selected
            [filePath] = .SelectedItems.Item(1)
        Else ' else dialog is cancelled
            MsgBox "You have cancelled the dialogue"
            [filePath] = "" ' when cancelled set blank as file path.
        End If
    End With
err:
    Exit Sub
End Sub

VBA to Select a Folder Path using Windows File Dialog

All you need to change is the type of the Dialog in the FileDialog Object. To explore the folders ONLy, you can provide the Dialog type as msoFileDialogFolderPicker


Sub browseFolderPath()
    On Error GoTo err
    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFolderPicker)
    
    'To allow or disable to multi select
    fileExplorer.AllowMultiSelect = False
    
    With fileExplorer
        If .Show = -1 Then 'Any folder is selected
            [folderPath] = .SelectedItems.Item(1)
        Else ' else dialog is cancelled
            MsgBox "You have cancelled the dialogue"
            [folderPath] = "" ' when cancelled set blank as file path.
        End If
    End With
err:
    Exit Sub
End Sub

In Above code, I am storing the selected path in a named range [filePath] or [FolderPath]. If you have a text box to store the selected file path you can replace it with YourTextBoxName.Text.

Download

Meanwhile over the weekend, you can download this workbook to play around with File explorer for selecting files or folders path. Have a fantastic weekend ahead.

Download this, use it and do not forget to provide me your feedback by typing your comment here or sending en email or you can twit me You can also share it with your friends colleagues.

Download Now

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

11 Comments

  1. Norm

    Thank you so very much Vishwamitra! Your code is easily understood and versatile. I have learned a lot from you in just one day. You are awesome.

    Reply
  2. Kali

    How to insert this code with other main VBA code ?

    For ex. I have to enter this code with excelmerge code.

    Reply
  3. Vaclav

    I have an issue with it – for me it is always using one folder above the selected ones. for example I have folder structure A-B-C-D When I will select folder D, the files will appear in folder C and so forth… Any ideas ?

    Reply
    • Vishwamitra Mishra

      can you share the piece of code which you are using to select the folder?

      Reply
  4. Jouhar

    Hi ,

    I am trying to upload a file to a web page, the following are the steps I followed:

    1. Open the web page

    2. Wait for until the page is getting loaded

    3. In this webpage I am uploading the file into the first “Upload a File” browser.

    4. Get the input element by tag name as “input”

    5. Hit the “browse” button, since the paste portion is disabled.

    6. Enter the file path in the “Choose File to Upload” window (path is in excel eg :D:\MArgin Discovery (Businness Assurance)\practice2.xlsx )

    7. Enter After 5th step, I am not able to enter the file path in the “Choose File to Upload” window, looks like the macro is not supporting for this.

    Here is my code:

    Sub File_Test()
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim HTMLButtons As MSHTML.IHTMLElementCollection
    Dim HTMLButton As MSHTML.IHTMLElement
    Dim ie As Object

    Set ie = CreateObject(“internetexplorer.application”)
    ie.Visible = True
    ie.navigate “http://www.htmlquick.com/reference/tags/input-file.html”

    Do While ie.readyState READYSTATE_COMPLETE
    Loop

    Set HTMLDoc = ie.document
    Set HTMLButtons = HTMLDoc.getElementsByTagName(“input”)

    For Each HTMLButton In HTMLButtons
    If HTMLButton.Type = “file” Then
    HTMLButton.Click
    HTMLButton.Value = “C:\Documents\Test\Temp.txt”
    Exit For
    End If
    Next

    Kindly help how to enter path name when input type is file.

    Thank you

    Regards,

    Jouhar

    Reply
  5. Partha

    I am looking to automate in VBA to Open a File (which the file path will be in a local variable) and I need to browse the File Explorer and pass this variable in (File Name: ) and should able to click on “OK” or “Open” button
    is there any code to do this activity. Please share if available.

    Please find my code below:

    Sub Browse_File()

    Dim file_path As String
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    file_path = ThisWorkbook.Sheets(2).Range(“B10”).value

    With fd

    .AllowMultiSelect = False
    .Title = “Please Select The File”
    .Filters.Clear
    .Filters.Add “Excel 2003”, “*.xlsx”
    .Filters.Add “All Files”, “*.*”
    .InitialFileName = file_path

    If .Show = -1 Then
    strFile = .SelectedItems(1)
    Application.SendKeys “{ENTER}” ‘ — Here I need to click on “OK” or “OPEN” button
    Else
    ‘Clicked Cancel
    End If

    End With

    End Sub

    Reply
  6. Manish

    Thanks buddy! It saved me a lot of time and very neatly done.

    Reply
  7. LALITA BAPU VIBHANDIK

    Thank you Sir its realy very hepfull.

    Reply
  8. LALITA BAPU VIBHANDIK

    how that file path can show in cell

    Reply
  9. Jason Smith

    Sub Browse_Folder_Path()
    On Error GoTo err
    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFolderPicker)

    ‘To allow or disable to multi select
    fileExplorer.AllowMultiSelect = False

    With fileExplorer
    If .Show = -1 Then ‘Any folder is selected
    [“TextBox 4”] = .SelectedItems.Item(1)
    Else ‘ else dialog is cancelled
    MsgBox “You have cancelled the dialogue”
    [“TextBox 4”] = “” ‘ when cancelled set blank as file path.
    End If
    End With
    err:
    Exit Sub
    End Sub

    any idea why it wont input into the text box?

    Reply

Submit a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest