What's New
Q & A
Tip Jar
C# Helper...
Follow VBHelper on Twitter Follow VBHelper on Twitter
MSDN Visual Basic Community
TitleWrite data into an Excel workbook in Visual Basic 6
DescriptionThis example shows how to write data into an Excel workbook in Visual Basic 6.
KeywordsMicrosoft Office, Office, Excel, Microsoft Excel, write to Excel, color cell, format cell, color Excel cell, format Excel cell

A Visual Basic 6 program can open the Excel application and use it as a server to manipulate Excel workbooks.

First add a reference to "Microsoft Excel 11.0 Object Library" (or whatever version you have installed on your system).

This example uses the following code to open a workbook, add a new worksheet to it, write to the worksheet, save the changes, and close everything.

' Write into the Excel workbook.
Private Sub cmdWrite_Click()
Dim excel_app As Excel.Application
Dim workbook As Excel.workbook
Dim sheet_name As String
Dim sheet As Excel.Worksheet
Dim header_range As Excel.Range
Dim values(1 To 4, 1 To 3) As Integer
Dim value_range As Excel.Range

    ' Get the Excel application object.
    Set excel_app = New Excel.Application

    ' Make Excel visible (optional).
    excel_app.Visible = True

    ' Open the workbook.
    Set workbook = _

    ' See if the worksheet already exists.
    sheet_name = Format$(Date, "MM-dd-yy")

    Set sheet = FindSheet(workbook, sheet_name)
    If (sheet Is Nothing) Then
        ' Add the worksheet at the end.
        Set sheet = workbook.Sheets.Add( _
            After:=workbook.Sheets(workbook.Sheets.Count), _
            Count:=1, _
        sheet.Name = sheet_name
    End If

    ' Add some data to individual cells.
    sheet.Cells(1, 1) = "A"
    sheet.Cells(1, 2) = "B"
    sheet.Cells(1, 3) = "C"

    ' Make that range of cells bold and red.
    Set header_range = sheet.Range("A1", "C1")
    header_range.Font.Bold = True
    header_range.Font.Color = vbRed
    header_range.Interior.Color = RGB(255, 128, 128)

    ' Add some data to a range of cells.
    values(1, 1) = 2
    values(1, 2) = 4
    values(1, 3) = 6
    values(2, 1) = 3
    values(2, 2) = 6
    values(2, 3) = 9
    values(3, 1) = 4
    values(3, 2) = 8
    values(3, 3) = 12
    values(4, 1) = 5
    values(4, 2) = 10
    values(4, 3) = 15
    Set value_range = sheet.Range("A2", "C5")
    value_range.Value2 = values

    ' Save the changes and close the workbook.
    workbook.Close SaveChanges:=True

    ' Close the Excel server.

    MsgBox "Done"
End Sub
First the code creates an Excel.Application object to control Excel. It makes that object visible so you can watch it work. Often you will not want to make the server visible.

Next the code opens a workbook. Most of the Open method's parameters are optional (things such as passwords and flags indicating that you want to open the file read-only) so only those that are needed are included in the call.

The code then calls the FindSheet function described shortly to see if the workbook contains a worksheet named after the current date. (Note that a worksheet's name cannot include the / character.)

If there is no worksheet with that name, the code creates one, adding it after the last worksheet, and sets the sheet's name.

Next the code sets the values of three cells individually. It then makes a Range representing those three cells and sets their Font.Bold, Color, and Interior.Color values.

The code then shows how to set worksheet values from an array. It creates a 2-dimensional array, makes a Range of the same size, and sets the Range's Value2 property to the array to set the cells' values.

Finally the code closes the workbook, saving the changes, and closes the Excel server.

The following code shows the FindSheet method.

' Return the worksheet with the given name.
Private Function FindSheet(ByVal workbook As Excel.workbook, _
    ByVal sheet_name As String) As Excel.Worksheet
Dim sheet As Excel.Worksheet

    For Each sheet In workbook.Sheets
        If (sheet.Name = sheet_name) Then
            Set FindSheet = sheet
            Exit Function
        End If
    Next sheet

    Set FindSheet = Nothing
End Function
This method simply loops through the workbook's worksheets to see if it contains one with the given name.
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.