Is there a way to make Microsoft Excel 2010 include the *.csv files in its list of default files in the file open dialogue box

Question

I am working with CSV files a lot and have to frequently navigate to different directories that contain CSV files. Whenever I press Ctrl + O to open a CSV file, Microsoft Excel 2010 will not list any CSV file because by default it will only show files with the extension *.xls, *.xlsx etc.

I, therefore, have to always configure the file open dialogue box to show the list of all files and then choose the CSV file that I intended to open.

Is there a way that I can figure Microsoft Excel 2010 to show the list of CSV files in the directory and file open dialogue box. If it is difficult to do this by configuring Microsoft Excel 2010, is it possible to achieve this by using a Microsoft Excel macro? Thank you for any inputs

Answer 1

I'm not aware of a way to change the way Excel's Open works, but you can certainly write a macro to do what you want. The following Sub uses the OpenFileDialog to choose a file with an extension of either "CSV", or anything beginning with "XL". You can adjust which extensions are included by changing the .Filters property.

Sub OpenFile()
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Add "Spreadsheets", "*.xl*; *.csv" 
        If .Show Then Application.Workbooks.Open (.SelectedItems(1))
    End With
End Sub

You can make this macro available in all workbooks by including it in a workbook called "Personal.xlsb" in your XLSTART folder (see Create and save all your macros in a single workbook). In Excel 2016, XLSTART is C:\Users[UserName]\AppData\Roaming\Microsoft\Excel\XLSTART (I'm not sure if it is in the same place in Excel 2010).