When you press Ctrl + O in Excel, by default it would list all Excel file types in the folder. This setting works perfectly fine for most users. However, if your work involves other file types, say CSV(comma-separated values). You would have to click on the file filter dropdown and select “Text Files (*.prn;*.txt;*.csv)” from the list.
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 Alt + F11 on your keyboard.
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 Alt + F8. This should open “Macro” dialog.
5. With “ThisWorkbook.CustomFileOpen” selected > Click Options and set up a hotkey to run this macro. For a familiar usage, you can set Ctrl + O as the shortcut.
Finally, to see if the code is working, switch back to Excel and press Ctrl + O. That should pop-up the Open File dialog.
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.
That’s all.