Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
Email Address * First Name *
By entering your email address you agree to receive emails from Excel Off The Grid. We'll respect your privacy and you can unsubscribe at any time.

Insert file path into cell with Browse button

In this post, I’ll show you the answer to a question that somebody recently sent me.

“If you would want to add a browse button right beside the cell that contains the file path so that you won’t need to manually copy and paste the file path in the cell, how would you do that?”

OK, let me show you the solution that I came up with.

Download the example file

I recommend you download the example file for this post.  Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download Icon
Download the file: 0052 Insert file path into cell with Browse button.zip

Watch the video


Watch the video on YouTube.

The solution

Let’s look at the solution in action.

Cell C3 contains a file path, with a folder icon next to it.

Example solution - final version

When we click on the folder icon, the file open dialog box opens.  We can then select a file and click Open.

Select File in the File Open Dialog Box

The new file path is now inserted into cell C3.

Example solution - updated file path

If you’re here to find out how to do this yourself, then you’re in the right place 🙂

Building the solution

Now we know what it does; let’s build it.

Create a named range

First, we need to create a named range for the cell containing the file path.

While this step isn’t essential, it reduces the risk of error.  If we write a macro that references cell C3, then insert a new row or column, the macro doesn’t change and is now referring to the wrong cell.  Therefore, named ranges help to remove this risk.

  1. Select the cell which contains the file path
  2. In the name box, type “filePath” and press Enter
    Create named range

First step. Done.

The VBA macro

The main driving force for this solution is a VBA macro.

If you have the Developer ribbon visible, click Developer > Visual Basic, or if not, press ALT + F11.  The Visual Basic Editor will open.

Developer - Visual Basic

Right-click the file in the Project window, then click Insert > Module from the menu.

Create a new module

A new code module will open.  Enter the following code into the code module

Enter code into code module

Sub selectFile()

'Create and set dialog box as variable
Dim dialogBox As FileDialog
Set dialogBox = Application.FileDialog(msoFileDialogOpen)

'Do not allow multiple files to be selected
dialogBox.AllowMultiSelect = False

'Set the title of the DialogBox
dialogBox.Title = "Select a file"

'Set the default folder to open
dialogBox.InitialFileName = "C:\Users\marks\Downloads\Example Folder"

'Clear the dialog box filters
dialogBox.Filters.Clear
'Apply file filters - use ; to separate filters for the same name
dialogBox.Filters.Add "Excel workbooks", "*.xlsx;*.xls;*.xlsm"

'Show the dialog box and output full file name
If dialogBox.Show = -1 Then
    ActiveSheet.Range("filePath").Value = dialogBox.SelectedItems(1)
End If

End Sub

The comments in the code describe the purpose of each section.

Please take careful note of the following, as you will need to change the code for your specific scenario.

ActiveSheet.Range(“filePath”).Value = dialogBox.SelectedItems(1)
Change the word “filePath” for the named range you created above.

dialogBox.InitialFileName = “C:\Users\marks\Downloads\Example Folder”
This contains the default file path where the folder will open each time.  Change this to match your environment.

dialogBox.Filters.Clear
d
ialogBox.Filters.Add “Excel workbooks”, “*.xlsx;*.xls;*.xlsm”
This code restricts the types of files which are shown.  The asterisk ( * ) is a wildcard character; any files ending in .xlsx, .xlsx, and .xlsm will be displayed.  To add new file types, PDFs for example, add “;*.pdf” into the text string.

Once the code has been entered, close the Visual Basic Editor by clicking on the [X] at the window’s top right.

100 Excel VBA Macros

Do you know the fastest way to learn foreign languages?  It is to read, write, speak, and think in that language as often as possible.  Apart from speaking, programming languages are no different.  The more you immerse yourself in that language, the faster you will pick it up.

100 Excel Macros Book

Therefore, what most people like you need is lots of examples that you can practice.  That is why the 100 Excel VBA Macros eBook exists.  It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers.  The book contains:

  • 100 example codes to practice reading and writing macros that will embed the language into your thinking.
  • An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
  • Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
  • Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.

Insert folder picture

Now we need something to act as the button to be clicked.  I am going to use an Icon.  Depending on the version of Excel you have, you may not have Icons.  But you can insert any picture or shape you wish.

To insert an icon like me, click Insert > Icons.

Insert Icon

Search for “Folder”, in the icons window.  Select the preferred icon and click Insert.

Insert Folder Icon

The image will now be inserted onto the face of the worksheet.

Move and resize the image to be in the right location.  I have also formatted the folder icon to be an orange color (similar to how folders look in the normal Windows environment).

Assign macro to picture

Finally, we assign the macro to the image.  Right-click on the image and select Assign macro… from the menu.

Assign Macro

From the Assign Macro window, click the selectFile macro (this is the name of the macro we created earlier), then click OK.

Select File - OK

Test it out

That’s it.  We’re done.  Now go and test it out.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

One thought on “Insert file path into cell with Browse button

  1. Blair Milster says:

    Would you like a non VBA based method of doing the above?

    Using MicroSoft 365 Excel, you have the option of using a formula to pull out the file name and path from.

    I use the hyperlink function starting with the path that the current file is saved it.

    That side steps security having issues with ‘rogue macro’ concerns.
    Example:
    =IF(ISNA(@CELL(“filename”,B1)),”After the file is saved this formula will display the path and name”,SUBSTITUTE(LEFT(@CELL(“filename”,B1),FIND(“]”,@CELL(“filename”,B1),1)-1),”[“,””,1))

    Gives you the path for the entire file.
    If you know you will be back to that file on a regular basis:
    paste the formula into the file
    F2 then press Enter to force recalc,
    copy/paste values back in my home worksheet.
    Trim to length for if I want to link to the folder or file desired.
    Wrap it in a hyperlink formula.
    Click save.

    Here is an example that lets you click to open the folder a file is saved in.

    =IF(ISNA(@CELL(“filename”,B2)),””,IF(ISERROR(FIND(“\”,@CELL(“filename”,A1),1)),HYPERLINK(LEFT(@CELL(“filename”,B2),FIND(“[“,@CELL(“filename”,B2),1)-1), “Saved in: “&MID(@CELL(“filename”,B2),FIND(“/[[“,SUBSTITUTE(@CELL(“filename”,B2),”/”,”/[[“,-1+SUMPRODUCT(LEN(CELL(“filename”,B2))-LEN(SUBSTITUTE(CELL(“filename”,B2),”/”,””)))),1)+1,(FIND(“/[“,@CELL(“filename”,B2))-2)-FIND(“/[[“,SUBSTITUTE(@CELL(“filename”,B2),”/”,”/[[“,-1+SUMPRODUCT(LEN(CELL(“filename”,B2))-LEN(SUBSTITUTE(CELL(“filename”,B2),”/”,””)))),1)+1)),HYPERLINK(LEFT(@CELL(“filename”,B2),FIND(“[“,@CELL(“filename”,B2),1)-1), “Saved in: “&MID(@CELL(“filename”,B2),FIND(“\[[“,SUBSTITUTE(@CELL(“filename”,B2),”\”,”\[[“,-1+SUMPRODUCT(LEN(CELL(“filename”,B2))-LEN(SUBSTITUTE(CELL(“filename”,B2),”\”,””)))),1)+1,(FIND(“\[“,@CELL(“filename”,B2))-2)-FIND(“\[[“,SUBSTITUTE(@CELL(“filename”,B2),”\”,”\[[“,-1+SUMPRODUCT(LEN(CELL(“filename”,B2))-LEN(SUBSTITUTE(CELL(“filename”,B2),”\”,””)))),1)+1))))

    Anyhow, I was stopping by to say “Thanks for posting some interesting examples and pointers from a POV that works.”

    Your writing style is a readable and effective at communicating.

    Thank you for taking the time to post and make it effective.

    Rgds,

    Blair

Leave a Reply

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