# VBForums UtilityBank > UtilityBank - Tutorials >  Tutorial/FAQ:  How to Automate Excel from VB6 (or VB5/VBA)

## si_the_geek

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


.

----------


## si_the_geek

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 "Excel_X_.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).


.

----------


## si_the_geek

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:
Dim oXLApp as Excel.Application       'Declare the object variable
   Set oXLApp = New Excel.Application  'Create a new instance of Excel
   oXLApp.Visible = True               'Show it to the user
   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).


.

----------


## si_the_geek

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:
Dim oXLApp as Excel.Application       'Declare the object variables
Dim oXLBook as Excel.Workbook       
   Set oXLApp = New Excel.Application  'Create a new instance of Excel
   Set oXLBook = oXLApp.Workbooks.Add  'Add a new workbook
   oXLApp.Visible = True               'Show it to the user
   Set oXLBook = Nothing               'Disconnect from Excel (let the user take over)
  Set oXLApp = Nothing

To open an existing workbook, just change the "  Set oXLBook =" line in the above example to this:

VB Code:
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:

  Set oXLApp = New Excel.Application  'Create a new instance of Excel
 Dim iSheetsPerBook as Integer         'Add a new workbook (with one sheet)
  iSheetsPerBook = oXLApp.SheetsInNewWorkbook
  oXLApp.SheetsInNewWorkbook = 1
  Set oXLBook = oXLApp.Workbooks.Add   
  oXLApp.SheetsInNewWorkbook = iSheetsPerBook
   oXLApp.Visible = True               'Show it to the user


.

----------


## si_the_geek

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:
Dim oXLApp as Excel.Application         'Declare the object variables
Dim oXLBook as Excel.Workbook
Dim oXLSheet as Excel.Worksheet
   Set oXLApp = New Excel.Application    'Create a new instance of Excel
  Set oXLBook = oXLApp.Workbooks.Add    'Add a new workbook
  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:
oXLApp.Visible = True                'Show it to the user
  Set oXLSheet = Nothing               'Disconnect from all Excel objects (let the user take over)
  Set oXLBook = Nothing
  Set oXLApp = Nothing
or, Part B(2) - Save the existing workbook you opened:

VB Code:
Set oXLSheet = Nothing             'disconnect from the Worksheet
  oXLBook.Close SaveChanges:= True   'Save (and disconnect from) the Workbook 
  Set oXLBook = Nothing
  oXLApp.Quit                        'Close (and disconnect from) Excel 
  Set oXLApp = Nothing
or, Part B(3) - Save the workbook to a new file:

VB Code:
Set oXLSheet = Nothing                        'disconnect from the Worksheet
  oXLBook.SaveAs "C:\My Documents\My File.xls"  'Save (and disconnect from) the Workbook 
  oXLBook.Close SaveChanges:= False
  Set oXLBook = Nothing
  oXLApp.Quit                                   'Close (and disconnect from) Excel 
  Set oXLApp = Nothing
or, Part B(4) - Close the workbook (but don't save):

VB Code:
Set oXLSheet = Nothing             'disconnect from the Worksheet
  oXLBook.Close SaveChanges:= False  'Close (and disconnect from) the Workbook
  Set oXLBook = Nothing
  oXLApp.Quit                        'Close (and disconnect from) Excel 
  Set oXLApp = Nothing

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


.

----------


## si_the_geek

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:
Dim my_variable As String 
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:
Dim my_variable As String 
my_variable = "hello"
oXLSheet.Cells(2, 1).Value = my_variable 
'or just this:
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:
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:
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:
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:
Dim lMyArray(2, 1) As Long
lMyArray(0, 0) = 1
lMyArray(0, 1) = 2
lMyArray(1, 0) = 3
lMyArray(1, 1) = 4
lMyArray(2, 0) = 5
lMyArray(2, 1) = 6
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:
Dim vArray As Variant
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:
Dim vArray As Variant
vArray  = oXLSheet.UsedRange.Value
'vArray now contains the values of ALL the cells in used range of the worksheet.
  'example usage of the array:
Dim lngCol as Long, lngRow as Long
  For lngRow = 1 To UBound(vArray,1)
    For lngCol = 1 To UBound(vArray,2)
      MsgBox vArray(lngRow, lngCol)
    Next lngCol
  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:
'create and fill a recordset here, called oRecordset
oXLSheet.Range("B15").CopyFromRecordset oRecordset
Note that you can also fill in the field names using an array like this:

VB Code:
Dim iCount As Integer
    With oXLSheet                    'Fill with data
      For iCount = 0 To (oRecordset.Fields.Count -1)
        .Cells(1, iCount+ 1) = oRecordset.Fields(iCount).Name
      Next iCount
      .Range("A2").CopyFromRecordset oRecordset
    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:
Dim iCount As Integer
Dim sFieldNames() As String
  ReDim sFieldNames(oRecordset.Fields.Count - 1) As String
  For iCount = 0 To (oRecordset.Fields.Count -1)
    sFieldNames(iCount) = oRecordset.Fields(iCount).Name
  Next iCount
  oXLSheet.Range("A1:" & xl_Col (1 + oRecordset.Fields.Count) & "1")

.

----------


## si_the_geek

You can format cells by code in a similar way to how you would manually. You can set things such as the font, the font style (bold, underline, etc), the alignment (left, right, etc) and the text colour.  You can apply these formats to any kind of range, the two we have looked at so far (Cells and Range) and a few new ones.


For example, to set the cell "A4" to be bold you can do the following:

VB Code:
oXLSheet.Range("A4").Font.Bold = True  '(False to turn bold off)
Or to set it bold, italic, and underlined, you can do this:

VB Code:
With oXLSheet.Range("A4")
       .Font.Bold = True      '(False to turn bold off)
       .Font.Italic = True    '(False to turn italic off)
       .Font.Underline = xlUnderlineStyleSingle 
    End With
Note that Underline does not use Boolean values, as there are various options for underlining (such as single or double underline).  When you type in the = after Font.Underline a list of the values should appear, so that you can simply select the appropriate one (the value to turn underlining off is xlUnderlineStyleNone).

To change the text colour of the same cell to green you can do this:

VB Code:
With oXLSheet.Range("A4")
       .Font.ColorIndex = 50 
    End With
To align the text within cells you can use a couple of extra properties, one for horizontal and one for vertical:

VB Code:
With oXLSheet.Range("A4")
       .HorizontalAlignment = xlRight    '(other options include xlCenter and xlLeft)
       .VerticalAlignment = xlBottom     '(other options include xlCenter and xlBottom)
    End With


The alternative range objects we are interested in are Rows and Columns.  Both of these take a single parameter, which is the row or column number we want to work with, and can be used in the same way as Cells or Range, eg:

VB Code:
oXLSheet.Rows(7).Font.Bold = True     '(False to turn bold off)
    oXLSheet.Columns(3).Font.Bold = True  '(False to turn bold off)
You can also select multiple adjacent columns/rows by providing a string parameter instead of a number, which contains the first and last row/column separated by a colon (if you provide a string for Columns, it must be the column name rather than number), eg:
VB Code:
oXLSheet.Rows("6:7").Font.Bold = True     '(False to turn bold off)
    oXLSheet.Columns("C:E").Font.Bold = True  '(False to turn bold off)

.

----------


## si_the_geek

As you may already know, when you are editing a spreadsheet manually you can press Ctrl and an arrow key to go to the next/last cell in that direction which contains text.  There are also methods of performing this via code.

Here is one method to find the last used row/column in the sheet:

VB Code:
LastRow = oXLSheet.UsedRange.Rows.Count
    LastCol = oXLSheet.UsedRange.Columns.Count
NB: UsedRange is a special pre-defined range which contains the entire used area of the sheet.


Here is an alternative - but note that this method assumes that every row has data in all columns (and vice versa). If this is not the case, it is safer to use the options above/below instead.

VB Code:
LastRow = oXLSheet.Range("A1").End(xlDown).Row
    LastCol = oXLSheet.Range("A1").End(xlToRight).Column

There is also another built-in function in Excel for detecting used ranges, the .SpecialCells function retrieves a number of "special range" values depending on the constant value passed to the function.  Here is the equivalent to the code above (works by going to the 'last' used cell):

VB Code:
LastRow = oXLSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
   LastCol = oXLSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

Some of the other parameters for the SpecialCells method are:
xlCellTypeAllValidation - Cells having validation criteriaxlCellTypeBlanks - Empty cellsxlCellTypeComments - Cells containing notesxlCellTypeFormulas - Cells containing formulasThe full list can be seen in the Help or the Object Browser.


.

----------


## si_the_geek

As you are probably aware there are several versions of Excel, all of which have subtle variations in the way they work, and the features that they provide.

In the vast majority of cases this will not affect the code you need to write to perform the tasks required on the document _(unless you use 'new' features which aren't available in the version of Excel which is installed)_. There can however be a problem when your program tries to start Excel.

The trouble is that with your installation package you installed a *specific* version of the Excel type library, which could easily be different from the version of Excel which is installed on the users computer.  Even though the installed version provides essentially the same functionality as the one on your development computer, Windows may realise that there are version differences and stop your program from using something that it thinks is likely to cause errors.

This has become even more pronounced recently, as versions of Excel now get you to install an "Excel.exe" file along with your program, rather than the traditional "Excel_X_.olb" file

Another issue is that Excel may not even be installed, which can stop your program from even starting!

The best way around these issues is to use a Late-Bound connection to Excel (see next section), with extra error handling around the first line (CreateObject/GetObject) to deal with the possibility that it isnt installed.


.

----------


## si_the_geek

The code so far in this tutorial has been Early-Bound (i.e.: VB knows in advance what Excel functions are available to you), which as seen in the previous section can cause serious issues.

Unless you can guarantee that all of your users have the same version of Excel installed as you do, I would strongly recommend converting to Late-bound for software that you release "into the wild".

The down-side to using this is that you no longer get some of the nice features of the VB IDE (like the drop-down lists of properties and methods that appear when you type oXLApp_._ ), so it may be a good idea to convert to this method once your code is finished.

There are four steps to convert your code:
1) Replace Excel data types with Object. 
If you have:

VB Code:
Dim oXLApp as Excel.Application
Dim oXLBook as Excel.Workbook
Dim oXLSheet as Excel.Worksheet
...

You need to change it to: 

VB Code:
Dim oXLApp as Object
Dim oXLBook as Object
Dim oXLSheet as Object
...
Note that you should also do this with any other variables that you have declared as Excel._something_


2) Change the initialisation of the application object. 

If you have: 
VB Code:
Set oXLApp = New Excel.Application

You should replace it with:

VB Code:
Set oXLApp = CreateObject("Excel.Application")
*Note:* if Excel isnt installed, this line of code will cause an error  you should deal with this by using error handling in an appropriate way for your project.


3) Define the constants that you have used.
Excel constants (e.g.: xlLeft) are defined in the object library; however you will no longer have a link to this, so they wont be defined any more!

There are two main options here; the first is to use the Object Browser to find the values/declarations for each one you have used (very slow, and prone to errors!), and the other (much simpler) is to add a pre-made module to your project which declares them all for you.  You can find a link to one which Microsoft produced in the useful functions and downloads section (post #13).


4) Remove "Excel object Library" from the list in "Project" -> "References"


Your code is now late-bound, and should work with all versions of Excel that support automation (as long as you havent used special functionality which wasnt available in earlier versions).


.

----------


## si_the_geek

If you find that after running your program you still have an open copy of Excel that you weren't expecting (either visible, or shown in Task Manager) then I'm afraid that you have made a mistake somewhere in your code.

The usual cause is using objects that are ok in Excel VBA code, but not in a VB application.  Common examples of this are:
_Application_ (which needs to be _oXLApp_)_Cells_ (which should be _oXLSheet.Cells_)_Range_ (which should to be _oXLSheet.Range_)_WorkSheets_ (which should be _oXLApp.WorkSheets_)_Selection_* (which needs to be _oXLApp.Selection_)_ActiveSheet_* (which should be a variable like oXLSheet)For any other Excel based items you use, you should do the same as all of the items in this list - specify the parent object (usually oXLSheet or oXLBook or oXLApp) that you want to refer to.

* As a side note, it is advisable to avoid using Selection and ActiveSheet altogether (even in Excel VBA code), as this can be changed by the user if they are working in Excel while your code is running - which means you could well be working in the wrong worksheet/workbook!


It may be that you haven't used appropriate code to close the sheet/workbook/application (see post #5 for valid examples).  This is particularly true if you have not used a variable to store the WorkSheet (as several people seem to do), as it is easy to refer to the WorkBook instead (which isn't technically valid, even tho it works); I would therefore strongly recommend using variable for it.

If as part of "closing down" you miss a child object variable (which is basically what happens in the situation described above) and destroy the application object, it will orphan the child object variable and cause the instance of Excel to remain because not all resources associated with the application instance have been closed and destroyed.

.

----------


## si_the_geek

There are many things which can be done with Excel that haven't been listed here, and there is a good reason - Excel will tell you the code you need!

If you record a macro in Excel, it is 'written' in VBA (a subset of VB), and this code can be copied almost directly into your VB program, with only a few modifications to use the objects you have created rather than the default Application/Sheet/etc objects _[failure to do this part will most likely cause your program to have bugs, or even cause your computer to crash]_.

To record a macro:
Go to "Tools" -> "Macro" -> "Record new macro".Manually do the things that you want your program to do.Press the "stop recording" button (a blue square).Go to "Tools" -> "Macro" -> "Macros...", which will show a list of macros.Select the macro you recorded, and click on "Edit" to see the code.


There are 4 steps you should perform when taking code from a macro to use in your program, they are:
Remove un-needed code (as Excel often does extra things, such as writing all values from an options screen).Change _ActiveDocument_, _Application_ etc to suit the variables in your code.Change _Selection_ to _oXLApp.Selection_.Prefix all instances of Range/Cells/Rows/Columns/etc with your sheet object.

Example 1:
The following code is created if you select a range, then set it to Bold and Italic:

VB Code:
Range("C9:F16").Select
    Selection.Font.Bold = True
    Selection.Font.Italic = True
In this case there is no un-needed code, so we can skip step 1.

There is no use of _ActiveDocument_ etc, so we can also skip step 2.

Following step 3 gives us this:

VB Code:
Range("C9:F16").Select
    oXLApp.Selection.Font.Bold = True
    oXLApp.Selection.Font.Italic = True
And step 4 gives us this:

VB Code:
oXLSheet.Range("C9:F16").Select
    oXLApp.Selection.Font.Bold = True
    oXLApp.Selection.Font.Italic = True
As mentioned in the previous post, it is best to avoid Selection altogether.  This can be done by changing the code to this:

VB Code:
oXLSheet.Range("C9:F16").Font.Bold = True
    oXLSheet.Range("C9:F16").Font.Italic = True
..or you can do it like this instead (a bit more efficient, and easier to read):

VB Code:
With oXLSheet.Range("C9:F16")
      .Font.Bold = True
      .Font.Italic = True
    End With


Example 2:
As another example, here is part of the code created if you go into "File"->"Page Setup", then set the orientation to Landscape, and set the "rows to repeat at top" to $1:$2

VB Code:
With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$2"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
... lots of lines removed! ...
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
    End With

Following step 1 gives us this:

VB Code:
With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$2"
    End With
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
    End With
Which can be shortened to this:

VB Code:
With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$2"
        .Orientation = xlLandscape
    End With
From there, following step 2 gives us this:

VB Code:
With oXLSheet.PageSetup
        .PrintTitleRows = "$1:$2"
        .Orientation = xlLandscape
    End With
In this case, there is nothing to do for steps 3 and 4.


.

----------


## si_the_geek

Excel constants module
This is a pre-written module which contains all of the Excel constants (e.g.: xlLeft), to enable you to keep the constant names in your code when you are using Late-binding.  Just add the module to your project!   (NB: this download also contains similar modules for the other Office products).

This is provided by Microsoft, and can be downloaded here: http://support.microsoft.com/kb/112671 (alternative)

Note that the above file is designed for Excel 97, so it does not contain constants which have been added more recently (it does however contain almost everything you need).  You can see a list of constants in the Excel 2003 constants page, which contains those for Excel 97 plus newer ones.


xl_col and xlColName 
Functions for converting column 'name' to/from column number.

Simply add these functions to your code (in a module if you want to use them from multiple forms).

VB Code:
Function xl_Col(ByRef Col_No) As String'returns Excel column name from numeric position (e.g.: col_no 27 returns "AA")'by Si_the_geek (VBForums.com)                                       'Only allow valid columns  If Col_No < 1 Or Col_No > 256 Then Exit Function   If Col_No < 27 Then                  'Single letter    xl_Col = Chr(Col_No + 64)  Else                                 'Two letters    xl_Col = Chr(Int((Col_No - 1) / 26) + 64) & _             Chr(((Col_No - 1) Mod 26) + 1 + 64)  End If End Function 'example usage:sColName = xl_Col(7)

VB Code:
Function xl_ColNo(Col_Name) As Integer'returns an Excel column number from its name (e.g.: col_name "AA" returns  27)'by Si_the_geek (VBForums.com)   Col_Name = UCase(Trim(Col_Name))  Select Case Len(Col_Name)  Case 1:     xl_ColNo = Asc(Col_Name) - 64  Case 2:     xl_ColNo = ((Asc(Left(Col_Name, 1)) - 64) * 26) _                       + (Asc(Right(Col_Name, 1)) - 64)  End Select End Function 'example usage:iColNo = xl_ColNo("Z")

.

----------


## si_the_geek

*Does the user need Excel installed?*
Yes (how can you automate something that isn't there?), and you cannot install it without them having a valid licence for it.


*What library version number should I use?*
To be honest any which you have installed will normally be fine, however if you want to use "new" functionality that has been added in newer versions of Excel, you need to select the one which provides that functionalility.

Since the introduction of Excel 95, the versions haven't been obvious (until then there were "proper" version numbers like 5.0).  The reference that you use in your program reflects the internal version number, rather than the displayed number.

The version numbers are:7.0 - Excel 958.0 - Excel 979.0 - Excel 200010.0 - Excel XP11.0 - Excel 2003

*How do I work with a copy of Excel that is already open?*
This will connect the application object to an open copy of Excel:

VB Code:
Set oXLApp = GetObject(,"Excel.Application")
Please note that there is no way to specify _which_ open copy of Excel to work with, so if there is more than one open you cannot predict which one will be referenced.

Note also that if there are no instances of Excel running, this line of code will cause an error.


*How do I call an Excel Macro from VB?*
See this example in the Microsoft Knowledge base:
http://support.microsoft.com/default...b;en-us;194611


*Do I really need a specific WorkBook/Worksheet/etc object?* 
Yes you do.  When you are testing it might not be a problem, but when your program is being used you may notice strange errors coming up, or your program failing, or (even worse) modifying the wrong documents.

Why would this happen?  Excel is capable of having multiple documents open, and can also be manipulated by the user.  If you don't reference specific WorkBooks/Worksheets/etc then you are at the mercy of which one Excel thinks you want - it usually assumes the one that is currently active.  If a user opens/creates a workbook then their workbook/sheet becomes active, rather than the one that you intended to work with.


*How do I detect events that happen in a visible workbook?*
See this thread:   http://www.vbforums.com/showthread.php?t=305203


.

----------


## si_the_geek

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".
_

.

----------

