How to import in Excel a csv file using VBA

Sometimes you need to read some data from a cvs file with Excel but you get frustrated with how Excel automatically imports the file if you try to open it directly.

Robust solution

You can use an Excel file with Macros. Copy and paste the following code in a module of your VBA project or under a Sheet code block (mouse right click on a sheet tab > show code).

Public Sub CSV_import()
' CSV_import macro function
' This function import a CSV file into a Sheet

    Dim sFile As String                                         ' sFile is the CSV file path
    Dim OpenFileError As String                                 ' This is need to handle error when the Open File Dialog Menu pops up.
    Dim MyWorkSheet As Worksheet                                ' The Sheet where data will be located, let call it Target Sheet

    Set MyWorkSheet = ActiveWorkbook.Sheets("TargetSheet")      ' Here we define the Target Sheet
    Sheets("TargetSheet").UsedRange.ClearContents               ' This deletes the sheet content in case there is something that it is disturbing

    With Application.FileDialog(msoFileDialogFilePicker)        ' This function opens the dialog window
        .InitialFileName = Application.ThisWorkbook.Path & "\"
        .AllowMultiSelect = False                               ' Enables to choose just one file
        .Filters.Add " File ", "*.txt; *.csv", 1                ' Filters just the CSV files
        If .Show = -1 Then                                      ' In case there is no error in opening the file
            sFile = .SelectedItems(1)                           ' sFile is defined as the selected file
            OpenFileError = "Error to open the file"            ' Showing errors in case the dialoaf function catched them
            MsgBox OpenFileError, , "Deferred Error Test"
        End If
    End With

    ' In the function below (Workbooks.OpenText) the content of the file is read
    ' Important parameters:
    '   StartRow (number): where to start to read
    '   Set True if you use one of these as a delimiter
    '       Tab:        Tabulation (\t) as delimiter.
    '       Semicolon:  ";" as delimiter.
    '       Comma:      "," as delimiter.
    '       Space:       " " as delimiter. There is a space between the quotation marks
    '   DecimalSeparator: ("." or ",")
    '   ThousandsSeparator (" " or "." or "," or anything else)

    Workbooks.OpenText Filename:=sFile, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:= _
        False, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array( _
        1, 1), Array(2, 1)), DecimalSeparator:=".", ThousandsSeparator:=" ", _

    ActiveSheet.UsedRange.Copy MyWorkSheet.Cells(1)         ' The data is "pasted" into our sheet
    ActiveWorkbook.Close SaveChanges:=False

End Sub

Assign that function CSV_import to a button to run it whenever you need to.

πŸ“Š Create a modern CV
Create a modern CV

Create a modern CV to impress recruiters