Page 1 of 1

Using Excel with Visual Basic 6! Rate Topic: -----

#1 Fib  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 161
  • View blog
  • Posts: 554
  • Joined: 12-March 09

Posted 20 May 2010 - 09:55 AM

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 :devil2: 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


Is This A Good Question/Topic? 3
  • +

Replies To: Using Excel with Visual Basic 6!

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Posted 23 May 2010 - 03:30 PM

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

Was This Post Helpful? 2
  • +
  • -

#3 Fib  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 161
  • View blog
  • Posts: 554
  • Joined: 12-March 09

Posted 24 May 2010 - 07:23 AM

Thanks for the corrections
Was This Post Helpful? 1
  • +
  • -

#4 JohnCollett  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 11-July 12

Posted 11 July 2012 - 07:05 AM

View PostFib, 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
Was This Post Helpful? 0
  • +
  • -

#5 Uncle Ben@Taiwan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 31-October 13

Posted 31 October 2013 - 01:48 AM

thanks Fib,

This is very helpful for a newbie like me.
Can you share some examples of making chart in excel using Vb6?
Was This Post Helpful? 0
  • +
  • -

#6 Parreño  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 30-January 14

Posted 30 January 2014 - 09:11 AM

Excellent tutorial!!

Is it possible to use an excel table on top of a VB6 form? have users manipulate excel data direcly on the form?

I'll do more research and if I find an answer I'll come back and show it to everybody.


Thanks
Was This Post Helpful? 0
  • +
  • -

#7 mcdczzz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 06-July 14

Posted 06 July 2014 - 08:07 AM

Sir do you have tutorial on how to make excel file using data from MS Access database?..because im looking up the for 3 days on how to this but i haven't seen one..what im trying to do is to make a report that can make excel file and also to print it..im a beginer so i cant realy keep up with objects..but this post is awesome..
Was This Post Helpful? 0
  • +
  • -

#8 MidniteSun  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 04-August 14

Posted 18 September 2014 - 08:14 AM

I too am a NOOB and am extremely grateful for this tutorial. It has helped me well. Having said that, the code falls apart in VB2010 express when I reach formatting.

The code tells me that "selection is not declared", I don't see it declared in the tutorial either. Will someone please post the correction that is needed in VB 2010 vs VB 6? Thank you very much!

'Examples of font formatting  
xlWorkSheet.Range("A15", "F25").Select()
With Selection.Font 'This is where VB 2010 express has an issue
            .Size = 8
            .Italic = True
            .Bold = True
            .Underline = True
            .Name = "Arial Black"
        End With

Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3518
  • View blog
  • Posts: 12,012
  • Joined: 12-December 12

Posted 18 September 2014 - 09:30 AM

I believe it should be excelApp.Selection throughout (this isn't specific to VB6).

But for VB.NET it is preferable to find a .NET specific Excel tutorial, rather than converting VB6 code. If, however, you've successfully managed to complete this tutorial then, fair enough!

This post has been edited by andrewsw: 18 September 2014 - 09:32 AM

Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3518
  • View blog
  • Posts: 12,012
  • Joined: 12-December 12

Posted 18 September 2014 - 09:35 AM

VB.NET and Excel
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1