[Fib]

Using Excel with Visual Basic 6!

Hello Everyone! I would like to present this tutorial to you because I see some people asking questions about connecting to Excel, and manipulating Excel sheets in the forum.

My background
In my work place, we use an Excel database. We have about 260 some excel files that hold all kinds of different data that is constantly being changed, and updated. Part of my job is to create tools that help manage this Excel database using Visual Basic 6. So I have acquired much knowledge about this subject through the countless number of tools I have created.

Lets begin!
I will be assuming that you have basic knowledge of programming, and Visual Basic 6 syntax. Such as variables, loops, conditional statements, arrays, ect.

The first thing you must do before writing code to manipulate Excel, is to add a reference to the Excel object library. Start up Visual Basic 6 and create a new Standard EXE project. Once you have your project, go to the Project drop down menu, and click References.

Project > References

Then in the list box, scroll down until you find "Microsoft Excel x Object Library" where x is the version. I have Microsoft Excel 12.0 Object Library. Once you find that check it and click OK.

Now that we are setup, we can start manipulating Excel with code.

Create Excel objects!
For this example, I will be writing code in the Form_Load() event.
To create an Excel object you must declare an Excel variable:

Dim excelApp As Excel.Application

After that, you must create the Excel object using the CreateObject() function.

Set excelApp = CreateObject("Excel.Application")

Whooh! Now we have an excel object setup and ready to go! But we really can't do much with the object in this state that is visible to us. So to fix that, we will next create a workbook within the object.

Set excelWB = excelApp.Workbooks.Add

Now we have a proper Excel application ready to do our bidding! If you set the visible property to true, and run the program, you will actually see an Excel application start up right before your eyes, without even having to press the little Excel shortcut icon! This is great stuff.

So this is what we have so far for the code.

Private Sub Form_Load()

    Dim excelApp As Excel.Application
    Dim excelWB As Excel.Workbook
    
    Set excelApp = CreateObject("Excel.Application")
    
    Set excelWB = excelApp.Workbooks.Add
    
    excelApp.Visible = True
    
End Sub

Adding data to a worksheet!
Now that you can actually see your creation, I'm sure you will want to manipulate it to carry out your evil tasks So lets create a worksheet variable so we can start adding some data to it.

    Dim excelWS As Excel.Worksheet
    Set excelWS = excelWB.Worksheets(1)

There are a couple of ways to access the individual cells on a sheet, the ones I use are the Cells() and Range() members of the worksheet object. The Cells() member accepts two integer values as parameters which specify the column and row you want to access. I'm not very sure what the Range() member accepts as parameters, but you can pass it two strings which specify the cells, or two Cells() objects. I will give you an example of the usage of these two members.

    'Examples of the Cells() member
    excelWS.Cells(1, 1).Value = "Testing testing..."
    
    For rowCounter = 2 To 10
        excelWS.Cells(rowCounter, 1).Value = "Using a loop to fill in cells"
    Next rowCounter
    
    'Examples of the Range() member
    excelWS.Range("A15", "F25").Value = "Using ranges with cell strings"
    
    excelWS.Range(excelWS.Cells(1, 4), excelWS.Cells(10, 5)).Value = "Using ranges with Cells() objects"

Formatting!
So now that we can add data to a sheet, how about we make that data look pretty. Lets look at some font formatting first. For all your font manipulating needs, there is a member function called Font, which then has many member variables to setup fonts.

    'Examples of font formatting
    excelWS.Range("A15", "F25").Select
    With Selection.Font
        .Size = 8
        .Italic = True
        .Bold = True
        .Underline = True
        .Name = "Arial Black"
    End With

Lets look at some number formatting and style now, such as increasing decimal length, and formatting for currency.

    'Examples of number formating
    excelWS.Range(excelWS.Cells(1, 2), excelWS.Cells(10, 4)).Value = 500
    excelWS.Range(excelWS.Cells(1, 2), excelWS.Cells(10, 2)).Select
    With Selection
        .NumberFormat = "$#,##0.00"
    End With
    
    excelWS.Range(excelWS.Cells(1, 3), excelWS.Cells(10, 3)).Select
    With Selection
        .NumberFormat = "#,##0.000"
    End With
    
    excelWS.Range(excelWS.Cells(1, 4), excelWS.Cells(10, 4)).Select
    With Selection
        .Style = "Percent"
    End With

Reading data from a worksheet!
Many times, I've needed to read in data from a worksheet in order to gather certain cells of data, and to perform calculations, ect. We will now save the workbook to your C: Drive, then reopen it in order to gather the data we put into it.

Lets first go over saving, and opening a workbook. You can find a list of file formats here: FileFormats

    'Save and close
    excelWB.SaveAs "C:\testing\testWB", FileFormat:=xlExcel8
    excelWB.Close

To open a workbook, there is of course an Open() member function within the workbook member.

Set excelWB = excelApp.Workbooks.Open("C:\testing\testWB.xls")

Except we are going to make it open on a button press. So on your main form, place a button somewhere. I didn't bother to rename my button so it will just be called Command1. Now double click the Command1 button to generate the click event code, or simply type it in manually.

Private Sub Command1_Click()

    Dim excelApp As Excel.Application
    Dim excelWB As Excel.Workbook
    Dim excelWS As Excel.Worksheet
    
    Set excelApp = CreateObject("Excel.Application")
    
    excelApp.Visible = True
    
    Set excelWB = excelApp.Workbooks.Open("C:\testing\testWB.xls")
    
End Sub

I recoded the variables in the click event sub because the other variables not be in scope in this sub. I do it this way because I don't like using global variables. If you want to use global variables you can, just delete the variables and place them at the top of your code.

So now, we have code that will open the same workbook we were adding data to on a button click! Very cool huh?

So now lets read some of that data in. The first thing I will do is figure out how many rows, and columns the sheet has in it filled with data. This is a very useful thing to do when reading in data.

    rowCount = excelWS.UsedRange.Rows.Count
    colCount = excelWS.UsedRange.Columns.Count

Now that we know the range of our data, lets check to see if we can find a certain piece of data. There is a few ways to do this, so I'll show you a couple.

' One way to search for things
    For r = 1 To rowCount
        For c = 1 To colCount
            If excelWS.Cells(r, c).Value = "Using ranges with cell strings" Then
                ' Store the row number, and column number in a variable
                foundRow = r
                foundCol = c
                excelWS.Cells(r, c).Value = "I found you!"
            End If
        Next c
    Next r
    
    ' Another way
    myRange = excelWS.Range("A2", "A10")
    Set foundRange = myRange.Find("Using a loop to fill in cells", , xlFormulas, xlWhole, xlByColumns)
    If Not (foundRange Is Nothing) Then
        'We found it
    End If

Adding and deleting worksheets!
This will be the last thing I cover in this massive tutorial. This will be quick and easy as it only takes a few lines of code. Both adding and deleting worksheets is contained within the workbook function.

'Delete the unused worksheets
    For Each sheet In excelWB.Sheets
        If Not sheet.Name = "Sheet1" Then
            sheet.Delete
        End If
    Next sheet
    
    ' Add a worksheet, then rename it
    excelWB.Worksheets.Add
    excelWB.Worksheets(1).Name = "NewlyCreated"
    
    excelWB.Worksheets(1).Cells(1, 1).Value = "I'm new!"

Well, this concludes this tutorial. There is so much more functionality to working with Excel than I gave here. But this should be a good starting point for anyone seeking the knowledge. I hope everyone enjoys.

Fib

Full code:

Private Sub Command1_Click()

    Dim excelApp As Excel.Application
    Dim excelWB As Excel.Workbook
    Dim excelWS As Excel.Worksheet
    Dim rowCount As Integer
    Dim colCount As Integer
    Dim r As Integer
    Dim c As Integer
    Dim foundRow As Integer
    Dim foundCol As Integer
    Dim foundRange As Range
    Dim myRange As Range
    Dim sheet As Worksheet
    
    Set excelApp = CreateObject("Excel.Application")
    
    excelApp.Visible = True
    
    Set excelWB = excelApp.Workbooks.Open("C:\testing\testWB.xls")
    Set excelWS = excelWB.Worksheets(1)
    
    rowCount = excelWS.UsedRange.Rows.Count
    colCount = excelWS.UsedRange.Columns.Count
    
    ' One way to search for things
    For r = 1 To rowCount
        For c = 1 To colCount
            If excelWS.Cells(r, c).Value = "Using ranges with cell strings" Then
                ' Store the row number, and column number in a variable
                foundRow = r
                foundCol = c
                excelWS.Cells(r, c).Value = "I found you!"
            End If
        Next c
    Next r
    
    ' Another way
    Set myRange = excelWS.Range("A2", "A10")
    Set foundRange = myRange.Find("Using a loop to fill in cells", , xlFormulas, xlWhole, xlByColumns)
    If Not (foundRange Is Nothing) Then
        'We found it
    End If
    
    'Delete the unused worksheets
    For Each sheet In excelWB.Sheets
        If Not sheet.Name = "Sheet1" Then
            sheet.Delete
        End If
    Next sheet
    
    ' Add a worksheet, then rename it
    Set sheet = excelWB.Worksheets.Add
    sheet.Name = "NewlyCreated"
    
    sheet.Cells(1, 1).Value = "I'm new!"
    
End Sub

Private Sub Form_Load()

    Dim excelApp As Excel.Application
    Dim excelWB As Excel.Workbook
    Dim excelWS As Excel.Worksheet
    Dim rowCounter As Integer
    
    Set excelApp = CreateObject("Excel.Application")
    
    excelApp.Visible = True
    excelApp.DisplayAlerts = False
    
    Set excelWB = excelApp.Workbooks.Add
    Set excelWS = excelWB.Worksheets(1)
    
    'Examples of the Cells() member
    excelWS.Cells(1, 1).Value = "Testing testing..."
    
    For rowCounter = 2 To 10
        excelWS.Cells(rowCounter, 1).Value = "Using a loop to fill in cells"
    Next rowCounter
    
    'Examples of the Range() member
    excelWS.Range("A15", "F25").Value = "Using ranges with cell strings"
    
    excelWS.Range(excelWS.Cells(1, 2), excelWS.Cells(10, 4)).Value = "Using ranges with Cells() objects"
    
    'Examples of font formatting
    excelWS.Range("A15", "F25").Select
    With Selection.Font
        .Size = 8
        .Italic = True
        .Bold = True
        .Underline = True
        .Underline = True
        .Name = "Arial Black"
    End With
    
    'Examples of number formating
    excelWS.Range(excelWS.Cells(1, 2), excelWS.Cells(10, 4)).Value = 500
    excelWS.Range(excelWS.Cells(1, 2), excelWS.Cells(10, 2)).Select
    With Selection
        .NumberFormat = "$#,##0.00"
    End With
    
    excelWS.Range(excelWS.Cells(1, 3), excelWS.Cells(10, 3)).Select
    With Selection
        .NumberFormat = "#,##0.000"
    End With
    
    excelWS.Range(excelWS.Cells(1, 4), excelWS.Cells(10, 4)).Select
    With Selection
        .Style = "Percent"
    End With
    
    'Save and close
    excelWB.SaveAs "C:\testing\testWB", FileFormat:=xlExcel8
    excelWB.Close
    
    Excel.Application.Quit
    Set excelApp = Nothing
    
End Sub


Private Sub Form_Terminate()
    
    Dim f As Form
    
    For Each f In Forms
        If f.hWnd <> Me.hWnd Then
            Unload f
            Set f = Nothing
        End If
    Next f
    
    Unload Me
    
End Sub

This post has been edited by Fib: 24 May 2010 - 07:25 AM

[thava]

Good job there
but there is some little corrections there

if you add a worksheet you don't know the index of the newly created worksheet, so change it like this

Dim e As Excel.Application
Dim b As Excel.Workbook
Dim s As Excel.Worksheet
Set e = New Excel.Application
e.Visible = True
Set b = e.Workbooks.Add
Set s = b.Worksheets.Add
s.Name = " Test "

the quit method only will not stop the application completely
set the application variable to nothing

e.Quit
Set e = Nothing

[Fib]

Thanks for the corrections

[JohnCollett]

Fib, on 24 May 2010 - 07:23 AM, said:

Thanks for the corrections

Is it possible to use the Excel Object Library in VB6 on a client pc that does not have Excel installed?

All I am doing is opening the xls file and then immediately saving it as a tab delimited text file to parse with routines I use all the time. So if the answer to my question is NO, then any suggestions on my small task would be appreciated.

Thanks