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
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
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
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
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.
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.
You are most welcome 🙂
How to insert this code with other main VBA code ?
For ex. I have to enter this code with excelmerge code.
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 ?
can you share the piece of code which you are using to select the folder?
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
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
Thanks buddy! It saved me a lot of time and very neatly done.
Thank you Sir its realy very hepfull.
how that file path can show in cell
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?