How to automate Excel with Late Binding?

[deko]

So I've decided to convert from Early Binding to Late Binding. Now that
I've been baptized, I need some instruction in the faith.

My former ways were thus:

Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")

and then I went off in debauchery like this:

lr = xlapp.Workbooks(strXlsFile).Worksheets(sn).Range(" A1").SpecialCells _
(xlCellTypeLastCell).Row
lc = xlapp.Workbooks(strXlsFile).Worksheets(sn).Range(" A1").SpecialCells _
(xlCellTypeLastCell).Column

Now that I've seen the light, I've amended my ways thusly:

Dim xlapp As Object
Set xlapp = CreateObject("Excel.Application")
(and removed the sinful reference to Microsoft Excel 11.0 Object Library)

But now, the code I so enjoyed above no longer works. I get a complie error
on xlCellTypeLastCell - "Variable not defined"

How do I use Excel automation with Late Binding?

Thanks in advance.

[Steve Jorgensen]

The problem is that xlCellTypeLastCell is a constant defined in the Excel
library that you are no longer referencing. Unfortunately, you just have to
redefine the constant in your own application. To find out what the value of
the constant needs to be, go into an Excel project, open the object browser.

By the way, be careful to define your constants using the same type. There
are a few oddball cases in MS Office where if you pass, say, 2 as an Integer,
you get a type mismatch error, but if you pass 2 as a Long it works.

On Sat, 18 Jun 2005 02:07:54 GMT, "deko" <de**@deko.com> wrote:

So I've decided to convert from Early Binding to Late Binding. Now that
I've been baptized, I need some instruction in the faith.

My former ways were thus:

Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")

and then I went off in debauchery like this:

lr = xlapp.Workbooks(strXlsFile).Worksheets(sn).Range(" A1").SpecialCells _
(xlCellTypeLastCell).Row
lc = xlapp.Workbooks(strXlsFile).Worksheets(sn).Range(" A1").SpecialCells _
(xlCellTypeLastCell).Column

Now that I've seen the light, I've amended my ways thusly:

Dim xlapp As Object
Set xlapp = CreateObject("Excel.Application")
(and removed the sinful reference to Microsoft Excel 11.0 Object Library)

But now, the code I so enjoyed above no longer works. I get a complie error
on xlCellTypeLastCell - "Variable not defined"

How do I use Excel automation with Late Binding?

Thanks in advance.

[deko]

> The problem is that xlCellTypeLastCell is a constant defined in the Excel

library that you are no longer referencing. Unfortunately, you just have to redefine the constant in your own application. To find out what the value of the constant needs to be, go into an Excel project, open the object browser.
By the way, be careful to define your constants using the same type. There are a few oddball cases in MS Office where if you pass, say, 2 as an Integer, you get a type mismatch error, but if you pass 2 as a Long it works.

I see.

So I tried this:

'Excel Constants ============================
Private Const xlCellTypeLastCell As Long = 11
Private Const xlCenter As Long = -4108
Private Const xlColumns As Long = 2
Private Const xlLineMarkers As Long = 65
Private Const xlCategory As Long = 1
Private Const xlPrimary As Long = 1
Private Const xlValue As Long = 2
Private Const xlThin As Long = 2
Private Const xlContinuous As Long = 1
Private Const xlDiamond As Long = 2
Private Const xlY As Long = 1
Private Const xlBoth As Long = 1
Private Const xlCustom As Long = -4114
Private Const xlNone As Long = -4142
Private Const xlDot As Long = -4118
Private Const xlCategoryScale As Long = 2
Private Const xlMedium As Long = -4138
Private Const xlAutomatic As Long = -4105
'===========================================
(since I have more than one function that uses these constants)
(by the way - do they all need to be Longs? - the Excel Object Browser did
not specify a data type)

In my first function I have this:

Dim xlapp as Object
Set xlapp = CreateObject("Excel.Application")

And then I pass xlapp to another Function like this:

Private Function CreateBBcharts(blnSummary As Boolean, _
frm As Form, sn As String, strStop As String, strTitle As String, _
strXlsFile As String, xlapp As Object) As Boolean

Formerly I did this:

Private Function CreateBBcharts(blnSummary As Boolean, _
frm As Form, sn As String, strStop As String, strTitle As String, _
strXlsFile As String, xlapp As Excel.Application) As Boolean

Seems to be working...

Anything else I should know about Late Binding in this context?

Thanks for your help!!

[Steve Jorgensen]

On Sat, 18 Jun 2005 02:49:11 GMT, "deko" <de**@deko.com> wrote:

The problem is that xlCellTypeLastCell is a constant defined in the Excel
library that you are no longer referencing. Unfortunately, you just have

to

redefine the constant in your own application. To find out what the value

of

the constant needs to be, go into an Excel project, open the object

browser.

By the way, be careful to define your constants using the same type.

There

are a few oddball cases in MS Office where if you pass, say, 2 as an

Integer,

you get a type mismatch error, but if you pass 2 as a Long it works.

I see.

So I tried this:

'Excel Constants ============================
Private Const xlCellTypeLastCell As Long = 11
Private Const xlCenter As Long = -4108
Private Const xlColumns As Long = 2
Private Const xlLineMarkers As Long = 65
Private Const xlCategory As Long = 1
Private Const xlPrimary As Long = 1
Private Const xlValue As Long = 2
Private Const xlThin As Long = 2
Private Const xlContinuous As Long = 1
Private Const xlDiamond As Long = 2
Private Const xlY As Long = 1
Private Const xlBoth As Long = 1
Private Const xlCustom As Long = -4114
Private Const xlNone As Long = -4142
Private Const xlDot As Long = -4118
Private Const xlCategoryScale As Long = 2
Private Const xlMedium As Long = -4138
Private Const xlAutomatic As Long = -4105
'===========================================
(since I have more than one function that uses these constants)
(by the way - do they all need to be Longs? - the Excel Object Browser did
not specify a data type)

In my first function I have this:

Dim xlapp as Object
Set xlapp = CreateObject("Excel.Application")

And then I pass xlapp to another Function like this:

Private Function CreateBBcharts(blnSummary As Boolean, _
frm As Form, sn As String, strStop As String, strTitle As String, _
strXlsFile As String, xlapp As Object) As Boolean

Formerly I did this:

Private Function CreateBBcharts(blnSummary As Boolean, _
frm As Form, sn As String, strStop As String, strTitle As String, _
strXlsFile As String, xlapp As Excel.Application) As Boolean

Seems to be working...

Anything else I should know about Late Binding in this context?

Not that I can think of. You should be good to go. If you do Excel calls
from other modules, you'll ahve to make those constants Public is all.