Load a CSV (comma-separated value) file into an Excel spreadsheet "manually"

[nandagopal]

Load the file's contents into a string. Use Split to break the file into lines.

Create an Excel server. Loop through the lines in the file. For each line, use Split to break the line into fields and write the fields into the Excel spreadsheet.

Code Select Expand

Private Sub cmdLoad_Click()
Dim excel_app As Excel.Application
Dim row As Integer
Dim col As Integer
Dim file_contents As String
Dim file_lines As Variant
Dim line_fields As Variant
Dim max_col As Integer

    Screen.MousePointer = vbHourglass
    DoEvents

    ' Load the CSV file.
    file_contents = FileContents(txtFromFile.Text)

    ' Create the Excel application.
    Set excel_app = CreateObject("Excel.Application")

    ' Uncomment this line to make Excel visible.
'    excel_app.Visible = True

    ' Create a new spreadsheet.
    excel_app.Workbooks.Add

    ' Loop through each row in the file.
    file_lines = Split(file_contents, vbCrLf)
    For row = 0 To UBound(file_lines)
        ' Process this line.
        line_fields = Split(file_lines(row), ",")
        For col = 0 To UBound(line_fields)
            ' Add this cell to the spreadsheet.
            excel_app.ActiveSheet.Cells(row + 1, col + 1) = _
                line_fields(col)
        Next col

        If max_col < col Then max_col = col
    Next row

    ' Autofit the columns.
    excel_app.ActiveSheet.UsedRange.Select
    excel_app.Selection.Columns.AutoFit

    ' Highlight the first row (column headers).
    excel_app.ActiveSheet.Range( _
        excel_app.ActiveSheet.Cells(1, 1), _
        excel_app.ActiveSheet.Cells(1, max_col)).Select

    With excel_app.Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 5
    End With

    ' Save the results.
    excel_app.ActiveWorkbook.SaveAs _
        FileName:=txtExcelFile.Text, _
        FileFormat:=xlNormal, _
        Password:="", _
        WriteResPassword:="", _
        ReadOnlyRecommended:=False, _
        CreateBackup:=False

    ' Comment the rest of the lines to keep
    ' Excel running so you can see it.

    ' Close the workbook without saving.
    excel_app.ActiveWorkbook.Close False

    ' Close Excel.
    excel_app.Quit
    Set excel_app = Nothing

    Screen.MousePointer = vbDefault
    MsgBox "Ok"
End Sub