0

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

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).

| improve this answer | |
  • Thank you for a very useful. The code works out of the box and I'm using it right now. Thank you again – Ramana Sep 19 '18 at 4:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.