{"id":4811,"date":"2016-12-02T07:11:41","date_gmt":"2016-12-02T07:11:41","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/wp\/?p=4811"},"modified":"2022-08-12T12:13:28","modified_gmt":"2022-08-12T12:13:28","slug":"how-to-open-file-explorer-in-vba","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2016\/12\/how-to-open-file-explorer-in-vba\/","title":{"rendered":"Windows FileDialog to Select File or Folder Path in VBA"},"content":{"rendered":"
Dear friends,<\/p>\n
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.
\n
File Dialog – File Picker<\/p><\/div><\/p>\n
You can say what is a big deal in it to create a button in Excel. Anyone can add a button from developer tab<\/a>. 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 <\/a> look like any stylish webpage buttons. Design Buttons in Excel<\/p><\/div><\/p>\n 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 \ud83d\ude42 How to assign Macro to an Image<\/p><\/div>\n FileDialog<\/strong> 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<\/i><\/strong> File Dialog in VBA<\/p><\/div><\/p>\n 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<\/i><\/strong><\/p>\n 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.<\/p>\n Meanwhile over the weekend, you can download this workbook to play around with File explorer for selecting files or folders path.<\/i> Have a fantastic weekend ahead.<\/i><\/strong><\/p>\n 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 <\/a> You can also share it with your friends colleagues. 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 […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1246,1679,1675,1676],"tags":[],"class_list":["post-4811","post","type-post","status-publish","format-standard","hentry","category-macro","category-excel-macro-beginner","category-excel-macro-for-beginners","category-excel-tips"],"yoast_head":"\n
\nNow, 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 \ud83d\ude42
\n<\/a>
How to assign a macro to an Image button<\/h3>\n
\nTo work like a button you need to create a Sub procedure<\/strong> i.e. in other words macro which you want to execute on clicking this image or shapes.
\n
\nNow right-click on the image\/shape which you have added and click on Assign Macro as shown in the below image<\/p>\n<\/a>
Code to make a browse button work <\/h3>\n
\n<\/a>
VBA to Select a File Path using Windows File Dialog<\/h2>\n
\r\nSub browseFilePath()\r\n On Error GoTo err\r\n Dim fileExplorer As FileDialog\r\n Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)\r\n \r\n 'To allow or disable to multi select\r\n fileExplorer.AllowMultiSelect = False\r\n \r\n With fileExplorer\r\n If .Show = -1 Then 'Any file is selected\r\n [filePath] = .SelectedItems.Item(1)\r\n Else ' else dialog is cancelled\r\n MsgBox \"You have cancelled the dialogue\"\r\n [filePath] = \"\" ' when cancelled set blank as file path.\r\n End If\r\n End With\r\nerr:\r\n Exit Sub\r\nEnd Sub\r\n<\/code><\/pre>\n
VBA to Select a Folder Path using Windows File Dialog<\/h2>\n
\r\nSub browseFolderPath()\r\n On Error GoTo err\r\n Dim fileExplorer As FileDialog\r\n Set fileExplorer = Application.FileDialog(msoFileDialogFolderPicker)\r\n \r\n 'To allow or disable to multi select\r\n fileExplorer.AllowMultiSelect = False\r\n \r\n With fileExplorer\r\n If .Show = -1 Then 'Any folder is selected\r\n [folderPath] = .SelectedItems.Item(1)\r\n Else ' else dialog is cancelled\r\n MsgBox \"You have cancelled the dialogue\"\r\n [folderPath] = \"\" ' when cancelled set blank as file path.\r\n End If\r\n End With\r\nerr:\r\n Exit Sub\r\nEnd Sub\r\n<\/code><\/pre>\n
Download <\/h2>\n
\n
\n
\n<\/a> <\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"