VBA – Tips & Tricks

VBA – Import CSV file

This blogpost’s reason d’etre is to show an alternative to the function “Import text” when importing af CSV file into an Excel file. And why not use Import text? Besides having to clean up after the import, the function is quite inefficient and is overkill to use on simple semicolon-separated files. Another reason is that the import function doesn’t handle foreign letters too well.

But just if anybody’s is wondering: Deleting the data connection and disabling the query definition after data import is done like this:

    'delete data connection
    ActiveWorkbook.Connections(Filnavn).Delete
    Dim qt As QueryTable
   'delete query connection
   For Each qt In ActiveSheet.QueryTables
            qt.Delete
    Next qt

The alternative is to read the CSV file as a text file, and split each line into an array, and looping through the array to print it in Excel. Credit to Lernkurve from providing the snippet here.

'This sub only provides the sub ImportCSVfile with parameters
Sub InitiateImportCSVFile() 
Dim filePath As String
Dim ImportToRow As Integer
Dim StartColumn As Integer
    
    
    filePath = "C:\Documents and Settings\MYCSVfile.csv"
    ImportToRow = 1 'the row where it will start printing
    StartColumn = 1 'the start column
    
    ImportCSVFile filePath, ImportToRow, StartColumn 
End Sub

'This is the sub that does all the work: 

Sub ImportCSVFile(ByVal filePath As String, ByVal ImportToRow As Integer, ByVal StartColumn As Integer)

    Dim line As String
    Dim arrayOfElements
    Dim element As Variant


    Open filePath For Input As #1 ' Open file for input
        Do While Not EOF(1) ' Loop until end of file
            ImportToRow = ImportToRow + 1
            Line Input #1, line
            arrayOfElements = Split(line, ";") 'Split the line into the array.
            
            'Loop thorugh every element in the array and print to Excelfile
            For Each element In arrayOfElements
                Cells(ImportToRow, StartColumn).Value = element
                StartColumn = StartColumn + 1
            Next
        Loop
    Close #1 ' Close file.
End Sub

And that’s it!