Newbie Question - Excel objects + Late binding

[Lenonardo]

I am writing an application that processes Excel spreadsheets.

I need this to work on the clients machine with Excel 2000.
I have Office XP Excel 2002 - Excel 10 object library.

If I add an Excel COM reference and use late binding to define my excel
application ( 'createobject("excel.application") can I cast this to
Excel.Application

e.g.
dim xlapp as Excel.application
xlapp=ctype(createobject("Excel.application"),Excel.application)

(which will be Excel 10.0 type) and then proceed as if everything is Excel
10.0 (although on the clients machine the underlying object will actually
be Excel 9.0)

e.g.
dim xl_wb as Excel.Workbook
xl_wb=xlapp.Workbooks.add

or do I need to do a CTYPE on every object created

xl_wb=ctype(xlapp.workbooks.add,excel.workbook)

or won't this work at all and I need to create each object as a pure Object
type as below?

dim xl_wb as object
xl_wb=xlapp.workbooks.add


Any advice appreciated.

 

[Herfried K. Wagner [MVP]]

I am writing an application that processes Excel spreadsheets.

I need this to work on the clients machine with Excel 2000.
I have Office XP Excel 2002 - Excel 10 object library.

If I add an Excel COM reference and use late binding to define my excel
application ( 'createobject("excel.application") can I cast this to
Excel.Application

e.g.
dim xlapp as Excel.application
xlapp=ctype(createobject("Excel.application"),Excel.application)

(which will be Excel 10.0 type) and then proceed as if everything is Excel
10.0 (although on the clients machine the underlying object will actually
be Excel 9.0)

e.g.
dim xl_wb as Excel.Workbook
xl_wb=xlapp.Workbooks.add

or do I need to do a CTYPE on every object created

xl_wb=ctype(xlapp.workbooks.add,excel.workbook)

or won't this work at all and I need to create each object as a pure Object
type as below?

dim xl_wb as object
xl_wb=xlapp.workbooks.add

You will have to use the last solution, all other solutions will bind
you to a certain version of Excel.

 

[Cor]

Hi Leonardo,

Did you look at OleDB also?
Then you can access the Worksheet as a dataset.

Cor

 

[Lenonardo]

(e-mail address removed) (Herfried K. Wagner [MVP]) wrote in

You will have to use the last solution, all other solutions will bind
you to a certain version of Excel.

Thanks for the rapid and clear response.
(This was the only solution I could see that made sense.
But I'd seen so many examples of the other alternatives on my googling
that I thought I'd just check I wasn't going to waste my time doing it
this way.)

I'll also have a look at OleDB - but as I am searching across columns as
well as own rows and having to respecify formulae I think OleDB will be
even more laborious.

 

[Fred Morrison]

Could the utlimate solution be:

1. Develop all code with the variables defined as Excel.Application (or
Excel.Workbook or Excel.Worksheet, etc.) along with the COM reference to
whatever version of Excel is on your developer workstation. You'll have the
ability to use Intellisense, etc. during development.

2. Keep the code very generic (lowest common denominator) to avoid using
Excel 2002-only features that don't exist in Excel 2000 or Excel 97 (yes,
it's still out there!).

3. When ready to deploy, change the Excel variables from Excel.<whatever> to
just plain Object and remove the COM reference.

OK, so I forgot about Enumerations within the Excel object model; but, gee,
I have to leave something to the reader, right? Maybe somebody will type
them in manually (just the common ones like xlDown for finding the end of a
row of cells via myRng.End(xlDown) ) and donate them to all the poor souls
that need to use Excel automation with late binding ;-)