Results 1 to 15 of 15

Thread: Tutorial/FAQ: How to Automate Excel from VB6 (or VB5/VBA)

Hybrid View

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Tutorial/FAQ: How to Automate Excel from VB6 (or VB5/VBA)

    Do you want to create or read an Excel file from your program?

    Perhaps you want to create a report with some data, a few formulas, and some graphs, or maybe get some particular data out of a spreadsheet?

    The good news is that Excel has been designed to let you do all of this and more; you can treat it like other objects that you would use from VB, and you can perform programmatically all of the tasks that you can manually. In the rest of this tutorial you will find out how.

    Please note that you must have Excel installed to do any of this, and so must your users!


    Note that as an alternative to automation you can use an Excel file as a database, which does not require Excel to be installed - however it is not as easy to use as other database systems, due to unusual SQL syntax (eg: "SELECT * from `Sheet1$A1:`"). This method is not discussed in this document, please search the Database Development forum for previously asked questions which cover this.



    Contents
    2 - In the beginning - Adding a Reference to Excel
    3 - Opening Excel
    4 - Creating/opening a Workbook
    5 - Preparing to work with data (setting and closing App/Book/Sheet references)
    6 - Reading and writing data
    7 - Basic formatting
    8 - Finding "special" cells (bottom row etc)
    9 - Installation Issues
    10 - Using a Late-Bound connection to Excel
    11 - "Excel doesn't close"
    12 - How do I write code to … ? [a.k.a. recording macro's]
    13 - Useful functions and downloads
    14 - Frequently Asked Questions


    .

  2. #2

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    In the beginning - Adding a Reference to Excel

    To start your journey into the world of automating Excel, you first need to tell your program that you want to reference Excel, to do this:

    Go to the "Project" menu, and select "References". You will be presented with a long list of available references, just scroll down to "Microsoft Excel X.X Object Library" (where X.X is a version number - see post #14 for a list), then tick it and press OK.

    NB: if it isn't in the list, there is probably an error with your installation of Excel. You can try to select the reference file manually by clicking the "browse" button, the file you need it is likely to be called something like "ExcelX.olb" (for Excel XP or later it seems to be "Excel.exe" instead).


    As an aside, if you are releasing your software to various users then having a reference isn't the method you should use, but don't worry about it for now (this is the method you should use whilst developing your software, as it provides auto-complete features and Help while you are writing your code).


    .

  3. #3

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Opening Excel

    Once you have a reference set up, you can create an instance of Excel. To do this you declare an object variable with a data type of Excel.Application, and then you can do whatever you want with it, before closing/disconnecting from it.

    In this simple example we will start a new instance of an Excel application, display it, and then disconnect from it. You can copy this code and run it as it is:
    VB Code:
    1. Dim oXLApp as Excel.Application       'Declare the object variable
    2.  
    3.   Set oXLApp = New Excel.Application  'Create a new instance of Excel
    4.  
    5.   oXLApp.Visible = True               'Show it to the user
    6.  
    7.   Set oXLApp = Nothing                'Disconnect from Excel (let the user take over)

    NB: The final line is something that several people forget in this situation, but it is important - without it your program will take up memory which it isn't actually using, and after this part of your program has been run several times your computer may crash.


    You can connect to an already open Excel application instead of opening a new one (using GetObject - as we will see later), but for now it is better to just create a new instance, as it is more reliable and less confusing.

    You can also use CreateObject as a variation to "New ..", but we will also get to this later (post #10).


    .
    Last edited by si_the_geek; Aug 19th, 2006 at 11:23 AM.

  4. #4

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Creating/opening a Workbook

    The example in the previous section was very basic, and to be honest pretty pointless too. In order to actually do anything you need to have a Workbook to interact with.

    There are times when you want to create a new Workbook, and times when you want to work with an existing one.

    To create a new Workbook:
    VB Code:
    1. Dim oXLApp as Excel.Application       'Declare the object variables
    2. Dim oXLBook as Excel.Workbook      
    3.  
    4.   Set oXLApp = New Excel.Application  'Create a new instance of Excel
    5.  
    6.   Set oXLBook = oXLApp.Workbooks.Add  'Add a new workbook
    7.  
    8.   oXLApp.Visible = True               'Show it to the user
    9.  
    10.   Set oXLBook = Nothing               'Disconnect from Excel (let the user take over)
    11.   Set oXLApp = Nothing

    To open an existing workbook, just change the " Set oXLBook =" line in the above example to this:
    VB Code:
    1. Set oXLBook = oXLApp.Workbooks.Open("c:\my folder\my workbook.xls") 'Open an existing workbook

    By default a new Workbook has the number of sheets which are specified in the users options. You can set this option via code, but should change it back afterwards (else the user will have the option that you set). To do this you need a few extra lines around the "'Add a new workbook" line:
    VB Code:
    1.   Set oXLApp = New Excel.Application  'Create a new instance of Excel
    2.  
    3. Dim iSheetsPerBook as Integer         'Add a new workbook (with one sheet)
    4.   iSheetsPerBook = oXLApp.SheetsInNewWorkbook
    5.   oXLApp.SheetsInNewWorkbook = 1
    6.   Set oXLBook = oXLApp.Workbooks.Add  
    7.   oXLApp.SheetsInNewWorkbook = iSheetsPerBook
    8.  
    9.   oXLApp.Visible = True               'Show it to the user

    .

  5. #5

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Preparing to work with data (setting and closing App/Book/Sheet references)

    So far, we have got as far as having a Workbook open within the Excel application, which is getting better but doesn't let us do what we want - interact with the data!

    For those of you who have experience in this area, you will hopefully know already that we are missing a vital ingredient in our examples so far - the Worksheet. All data in a workbook is held in specific worksheets, so we should be using them.

    Now it is time to start with the proper examples. There are a few different ways of modifying/reading data, but all of them start with the same basic block of code, which we will call "Part A":
    VB Code:
    1. Dim oXLApp as Excel.Application         'Declare the object variables
    2. Dim oXLBook as Excel.Workbook
    3. Dim oXLSheet as Excel.Worksheet
    4.  
    5.   Set oXLApp = New Excel.Application    'Create a new instance of Excel
    6.   Set oXLBook = oXLApp.Workbooks.Add    'Add a new workbook
    7.   Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
    Note that you could open an existing workbook instead of creating a new one, by using the code in the previous post.

    This should be followed by some sort of modification/reading code, which we will come to in the next section - the important part for now is to finish properly, because if you don't then you won't see any of the changes you have made!

    When you have finished working with the data you can show, save, save as, or just close the workbook. There are different methods for each of these options, all of which we will call "Part B", which one of these you use is up to you (and will probably vary depending on the project you are writing at the time).

    Part B(1) - Show the workbook: (best while you are writing or testing!)
    VB Code:
    1. oXLApp.Visible = True                'Show it to the user
    2.   Set oXLSheet = Nothing               'Disconnect from all Excel objects (let the user take over)
    3.   Set oXLBook = Nothing
    4.   Set oXLApp = Nothing
    or, Part B(2) - Save the existing workbook you opened:
    VB Code:
    1. Set oXLSheet = Nothing             'disconnect from the Worksheet
    2.   oXLBook.Close SaveChanges:= True   'Save (and disconnect from) the Workbook
    3.   Set oXLBook = Nothing
    4.   oXLApp.Quit                        'Close (and disconnect from) Excel
    5.   Set oXLApp = Nothing
    or, Part B(3) - Save the workbook to a new file:
    VB Code:
    1. Set oXLSheet = Nothing                        'disconnect from the Worksheet
    2.   oXLBook.SaveAs "C:\My Documents\My File.xls"  'Save (and disconnect from) the Workbook
    3.   oXLBook.Close SaveChanges:= False
    4.   Set oXLBook = Nothing
    5.   oXLApp.Quit                                   'Close (and disconnect from) Excel
    6.   Set oXLApp = Nothing
    or, Part B(4) - Close the workbook (but don't save):
    VB Code:
    1. Set oXLSheet = Nothing             'disconnect from the Worksheet
    2.   oXLBook.Close SaveChanges:= False  'Close (and disconnect from) the Workbook
    3.   Set oXLBook = Nothing
    4.   oXLApp.Quit                        'Close (and disconnect from) Excel
    5.   Set oXLApp = Nothing

    Once you have these two chunks of code you are ready to start the fun.


    .

  6. #6

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Reading and writing data

    This code needs to be run on an open worksheet, so should be placed between "Part A" and "Part B" from the previous post. It is probably best to use B(1) for now, so that you can see what is going on more easily.


    a) using Cells, Range (and UsedRange)
    There are a few methods of working with the data depending on what you want to achieve.

    The simplest method is to read/write a single cell, which can be done using the Cells collection of the WorkSheet like this:
    VB Code:
    1. Dim my_variable As String
    2. my_variable = oXLSheet.Cells(2, 1).Value
    This copies the text in the cell A2 (specified in the brackets: row 2, column 1) to the variable "my_variable".

    To write a single cell you just need to change the order of the expression like this:
    VB Code:
    1. Dim my_variable As String
    2. my_variable = "hello"
    3. oXLSheet.Cells(2, 1).Value = my_variable
    4. 'or just this:
    5. oXLSheet.Cells(2, 1).Value = "hello"

    To read or write the Formula of the cell rather than the displayed text, just change the ".Value" to ".Formula", e.g.:
    VB Code:
    1. oXLSheet.Cells(3, 1).Formula = "=A1+2"

    All of the methods so far can also be used with the Range object instead of Cells, which allows you to specify the cell in the standard Excel "A1" address format, e.g.:
    VB Code:
    1. oXLSheet.Range("B4").Value = "range test"

    There is an advantage to using the Range object, in that you can work with multiple cells at the same time, for example you could set the value of all cells in the range D3:E5 to a single value like this:
    VB Code:
    1. oXLSheet.Range("D3:E5").Value = 3
    This may seem a little pointless, but with a little alteration you can see the power of this, as you can set all the values differently by using an array like this:
    VB Code:
    1. Dim lMyArray(2, 1) As Long
    2. lMyArray(0, 0) = 1
    3. lMyArray(0, 1) = 2
    4. lMyArray(1, 0) = 3
    5. lMyArray(1, 1) = 4
    6. lMyArray(2, 0) = 5
    7. lMyArray(2, 1) = 6
    8. oXLSheet.Range("D7:E9").Value = lMyArray
    The location of the cells within the range can be deduced from the array positions in the same way as for the Cells collection, i.e.: (row, column).

    Unfortunately you can't quite do this the other way around (i.e.: you can't set lMyArray equal to the range), as VB will not allow you to assign to an array. Instead you need to use a Variant to hold the array instead, e.g.:
    VB Code:
    1. Dim vArray As Variant
    2. vArray  = oXLSheet.Range("D7:E9").Value
    You can copy the entire used range of the spreadsheet simply using a special built-in range called UsedRange, like this:
    VB Code:
    1. Dim vArray As Variant
    2. vArray  = oXLSheet.UsedRange.Value
    3. 'vArray now contains the values of ALL the cells in used range of the worksheet.
    4.  
    5.  
    6. 'example usage of the array:
    7. Dim lngCol as Long, lngRow as Long
    8.   For lngRow = 1 To UBound(vArray,1)
    9.     For lngCol = 1 To UBound(vArray,2)
    10.       MsgBox vArray(lngRow, lngCol)
    11.     Next lngCol
    12.   Next lngRow

    Setting values in the spreadsheet using an array has the advantage of being far quicker, as each time you enter data Excel does some work like re-calculating formulas. Using an array means that this extra work is only done once for the entire array, rather than once per cell.

    b) using a RecordSet
    There is one more way of putting data into Excel that is extremely useful, which is to copy data directly from a recordset that you have gotten from a database. You simply say which is the first cell to put the data into, and Excel works out the rest of the cells that are required (although you can specify the maximum rows/columns to use in two optional parameters).

    I have not created the recordset in these examples, as there are many ways in which you can do it. If you need help with this, please see the tutorials (or post a new thread) in the Database Development forum on this site.
    VB Code:
    1. 'create and fill a recordset here, called oRecordset
    2. oXLSheet.Range("B15").CopyFromRecordset oRecordset
    Note that you can also fill in the field names using an array like this:
    VB Code:
    1. Dim iCount As Integer
    2.     With oXLSheet                    'Fill with data
    3.       For iCount = 0 To (oRecordset.Fields.Count -1)
    4.         .Cells(1, iCount+ 1) = oRecordset.Fields(iCount).Name
    5.       Next iCount
    6.       .Range("A2").CopyFromRecordset oRecordset
    7.     End With
    "But I thought setting individual Cells was slow?" It is, but to use Range you need to know the target cells, in order to do that you need to know the column address of "B" + oRecordset.Fields.Count, which requires a bit more work. Luckily I have provided a function in section 13 "Useful functions and downloads" called xl_Col, which returns the column name for the column number that you specify (there is also xl_ColNo, which converts the name back into a number). This function allows you to do use the Range object like this:
    VB Code:
    1. Dim iCount As Integer
    2. Dim sFieldNames() As String
    3.   ReDim sFieldNames(oRecordset.Fields.Count - 1) As String
    4.   For iCount = 0 To (oRecordset.Fields.Count -1)
    5.     sFieldNames(iCount) = oRecordset.Fields(iCount).Name
    6.   Next iCount
    7.   oXLSheet.Range("A1:" & xl_Col (1 + oRecordset.Fields.Count) & "1")

    .
    Last edited by si_the_geek; May 12th, 2006 at 08:04 AM. Reason: corrected "example usage of the array" (previously read rows as columns & vice-versa)

  7. #7

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    The End

    Well folks, thats all of it!

    Not everything is covered, so if you need any further help feel free to ask in the forums (Classic VB or Office Development would be best), as there are several people around (including myself) who can help you.

    If you have anything to add to this tutorial, or anything is not clear enough, please let me know via PM.



    Thanks to RobDog888 for his comments, as well as the details/example of .SpecialCells, and some of the text for "Excel doesn't close".


    .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width