Fog Creek Software
Discussion Board




VB6 Excel Automation

I am writing a custom VB6 app for a client that wants VB to generate Excel sheets on the fly and print them out. I am prepared to use the Excel automation objects in VB.

The issue here is that the client wants to be able to install the application on different client machines. Some run Excel '97, others 2000 & XP.

Can anyone provide me with some information or good links on how to have my VB code detect which version of Excel is installed on the client and how the Object models may differ? (Is this complicated?)

Thanks in advance....

KenB
Monday, April 14, 2003

Can't you just use CreateObject(,"Excel.Application") ?

If you check the registry entry for that class, you'll see that it's mapped to the correct version of Excel that's installed on the client machine.

In general, the object model of those 3 is equivalent for automatically generating sheets and printing them out.

Ankur
Monday, April 14, 2003

Using the OLE for creating Excel spreadsheets is very easy once you've got the hang of the well designed API.  The VBA help file supplied with Excel is excellent.  (It isn't installed as standard.)

The good news is that the API hardly varies between versions.  Unless your using a feature of a later version, compatability shouldn't be a problem.

If you do need to know the version, then use Application.version.  For example

Sub main()
    msgbox Application.Version
End Sub

Heres a good quick-start:
http://maths.sci.shu.ac.uk/units/20-2510/notes/intro.shtml

Ged Byrne
Monday, April 14, 2003

Thanx a lot - much appreciated. I will do that and use late binding so I am not "tied into" one particular version of Excel. (Glad to see that Microsoft didn't change the Object Model when just trying to do simple functions)

Thanks again!

KenB
Monday, April 14, 2003

I believe MS have a version of the "97" IDL you can use to program against available for download from their website.

In theory all later versions will create the same objects, it's just the older interfaces are hidden. (My registry seems to have both a Excel 5.0 and an Excel 10.0 library setup)

Worth checking this on a machine with "97" but it should all work fine.

Peter Ibbotson
Tuesday, April 15, 2003

Second thought, don't use late binding if you can.
I just checked on my Office 10 install and if you browse (When adding references in VB)  for \program files\microsoft office\Office 10\XL5EN32.OLB you get the Excel 5 Object library.
So the following code:

Private Sub Command1_Click()
    Dim n As Excel.Application
    Set n = CreateObject("Excel.Application")
    Debug.Print n.Version
    Set n = Nothing
End Sub

Should work on all versions without any big problems or with the performance hit of late bound objects.

Peter Ibbotson
Tuesday, April 15, 2003

KenB,

I was in your position a few months ago.

Suggest use late binding. Borrowing from the previous code sample by Peter Ibbotson, do not reference any of the Excel libraries and Dim n as Object before you compile for deployment.

When I am writing the code, I reference the Excel library on my computer and Dim n as Excel.Application to take advantage of Intellisense. However, before compiling for deployment, I remove the reference and define all the Excel objects that I use (Application, Workbook, and Worksheet, usually) as Object.

Himanshu Nath
Tuesday, April 15, 2003

*  Recent Topics

*  Fog Creek Home