Loading an CSV file into a workseet in Excel

Let’s assume we have an Excel worksheet with some nicely formatted headers and just above the row containing the headers we’ve placed a button which will we use to load a csv file into the rows below header row (without losing our formatting etc.)

Go to the Developer tab in Excel and select Visual Basic then create a subroutine like this

Sub LoadFile()
Dim fileName As String
   
   fileName = "c:\myfile.csv"

   Set ws = ActiveWorkbook.Sheets("Import")

   ws.Rows("3:" & Rows.Count).ClearContents
    
   With ws.QueryTables.Add(Connection:="TEXT;" & fileName, Destination:=ws.Range("A3"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .AdjustColumnWidth = False
        .Refresh
    End With
    
End Sub

I’ve simplified the code to have an explicit filename, instead of getting the filename via a folder selection dialog box or from a cell on the spreadsheet.

Simply get the active worksheet (mine’s named Import) and from this we’ll clear all row’s below our heading row (my rows start as row 3).

Next we use the QueryTables object and add the “Connection” supplying the filename that is to be imported. The destination is the start column/row where we want to place our data.

AdjustColumnWidth = False is useful to stop the import resizing your columns, the Refresh procedure will cause the spreadsheet to redraw itself.

Now this will load the CSV into the area below the column headings and not resize the columns to match the data size (hence not ruin existing formatting).