Import CSV to Excel - automatically "Text to columns" and "insert table"

Question

I'd like to open my CSV (comma-delimited) file on Excel 2010 and automagically convert text to columns and then select all active cells and insert table with headers.

Is it possible to add a button to my ribbon that would do all this for me?

I quite often work with CSV files with different sizes and I find it to be bit of a pain to do this manually each time.

Solution

A bit late for this, but I just ran across the question...

This is for selecting specific files from a picker:

Sub OpenCSV()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.AllowMultiSelect = True
    fd.Show
    For Each fileItem In fd.SelectedItems
        Workbooks.OpenText Filename:= _
            fileItem _
            , Origin:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True
    Next
End Sub

This will open all CSV files in a chosen folder:

Sub OpenCSVFolder()
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    fd.AllowMultiSelect = True
    fd.Show
    For Each folderItem In fd.SelectedItems
        fileItem = Dir(folderItem & "\" & "*.csv")
        While fileItem <> ""
            Workbooks.OpenText Filename:= _
                folderItem & "\" & fileItem _
                , Origin:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
                xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
                Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True
            fileItem = Dir
        Wend
    Next
End Sub

Note that these files are set to Tab Delimited - change the delimiter by updating the Tab:=True or Comma:=False parameters.