When you press
We can save these additional clicks by using a VB Macro to see only those file types which we want as in the image below.
Follow below steps to set this up:
1. Launch Microsoft Excel > Create a new document.
2. Navigate to Developer tab > Click on Visual Basic, or just press
3. In the project panel, double-click on “ThisWorkbook” under VBAProject. Now, paste below code in to the Editor:
Sub CustomFileOpen() Dim strFileToOpen As String strFileToOpen = Application.GetOpenFilename( _ FileFilter:="Spreadsheets (*.xl* ; *.ods; *.csv),*.xl; *.ods; *.csv", _ Title:="Select Spreadsheet to Open") End Sub
4. Switch back to Excel > Press
5. With “ThisWorkbook.CustomFileOpen” selected > Click Options and set up a hotkey to run this macro. For a familiar usage, you can set
Finally, to see if the code is working, switch back to Excel and press
In this script, we have made two changes to the dialog:
1. Changed the dialog title to “Select Spreadsheet to Open.”
2. Added filter to display all Excel files, OpenDocument Spreadsheet (.ods), and Comma-separated values (.csv) files.
You can further tweak the script to include or exclude any file type you want by editing the fifth line.
Tip: To make this macro available by default in all Excel Sheets, save the code in a workbook called “Personal.xlsb” in your XLSTART folder. This folder is located at:
C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART. Replace “UserName” in the path with your own Windows username.