Importing CSV Files and Converting to Tables with VBA

Automate the process of importing CSV data files and outputting them to tables using Excel VBA scripts.

The VBA script to open a file, create a table and output is as follows. (Partially quoted below)

Change “C:\pg\node_tmp\out.csv” in the code below to any file name and file path, and change “Sheet1” to the specified sheet name.

Sub openfile()

    Set ws = ActiveWorkbook.Sheets("Sheet1")
    With ws.QueryTables.Add(Connection:="TEXT;" & "C:\pg\node\_tmp\out.csv", Destination:=ws.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
    End With

    On Error GoTo nothingtodelete
        Sheets("Data").QueryTables(1).SaveData = False
        Sheets("Data").QueryTables.Item(1).Delete
nothingtodelete:

End Sub

The following program is an example of a VBA script that automatically executes the following program at startup when opening a file linked to the following VBA in Excel and reads a csv file. The following VBA program is saved under ThisWorkbook in the project.

Sub openfile()

    Set ws = ActiveWorkbook.Sheets("Sheet1")
    With ws.QueryTables.Add(Connection:="TEXT;" & "C:\pg\node\_tmp\out.csv", Destination:=ws.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
    End With

    On Error GoTo nothingtodelete
        Sheets("Data").QueryTables(1).SaveData = False
        Sheets("Data").QueryTables.Item(1).Delete
nothingtodelete:

End Sub

Private Sub Workbook_Open()

    Call openfile
    Call colorize

    Dim dummy As Integer
    dummy = 0
End Sub

Note that some or all of the VBA programs in this article are quoted from the following.

vba - Is there a way to import data from .csv to active excel sheet? - Stack Overflow User hatchnet - Stack Overflow

Reference:

vba - Is there a way to import data from .csv to active excel sheet? - Stack Overflow