Use Visual Basic to write an Excel spreadsheet

[nandagopal]

Create an Excel server object and use its methods to write to the new workbook.

Note that this project contains a reference to the Excel object library. You may need to use a different reference for your version of Excel.

Code Select Expand

Private Sub cmdLoad_Click()
Dim excel_app As Excel.Application
Dim row As Integer

    Screen.MousePointer = vbHourglass
    DoEvents

    ' 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

    ' Insert data into Excel.
    With excel_app
        .Range("A1").Select
        .ActiveCell.FormulaR1C1 = "Title"
        .Columns("A:A").ColumnWidth = 35
        With .Selection.Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 5
        End With

        .Columns("B:B").ColumnWidth = 13
        .Range("B1").Select
        .ActiveCell.FormulaR1C1 = "ISBN"
        With .Selection.Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 10
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 5
        End With

        row = 2
        .Range("A" & Format$(row)).Select
        .ActiveCell.FormulaR1C1 = "'Advanced Visual Basic " & _
            "Techniques"
        .Range("B" & Format$(row)).Select
        .ActiveCell.FormulaR1C1 = "'0-471-18881-6"

        row = row + 1
        .Range("A" & Format$(row)).Select
        .ActiveCell.FormulaR1C1 = "'Ready-to-Run Visual " & _
            "Basic Algorithms"
        .Range("B" & Format$(row)).Select
        .ActiveCell.FormulaR1C1 = "'0-471-24268-3"

        row = row + 1
        .Range("A" & Format$(row)).Select
        .ActiveCell.FormulaR1C1 = "'Custom Controls Library"
        .Range("B" & Format$(row)).Select
        .ActiveCell.FormulaR1C1 = "'0-471-24267-5"

        row = row + 1
        .Range("A" & Format$(row)).Select
        .ActiveCell.FormulaR1C1 = "'Bug Proofing Visual " & _
            "Basic"
        .Range("B" & Format$(row)).Select
        .ActiveCell.FormulaR1C1 = "'0-471-32351-9"

        row = row + 1
        .Range("A" & Format$(row)).Select
        .ActiveCell.FormulaR1C1 = "'Ready-to-Run Visual " & _
            "Basic Code Library"
        .Range("B" & Format$(row)).Select
        .ActiveCell.FormulaR1C1 = "'0-471-33345-X"

        row = row + 1
        .Range("A" & Format$(row)).Select
        .ActiveCell.FormulaR1C1 = "'Visual Basic Graphics " & _
            "Programming"
        .Range("B" & Format$(row)).Select
        .ActiveCell.FormulaR1C1 = "'0-471-35599-2"

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

    ' 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