Excel OLE Automation: How to access constants ?

[SDelroen]

Hi,

I am doing some Excel OLE Automation, but I can't find out how to access the
constants.

Let's say I want to use the Range("C65536").End(xlUp) to find the last cell
in a column.

The problem is my program always fails because the constant xlUp is not
defined.

Is there any way to access those constants through the COM interface ?

Thank you very much !

PS: I am using this interface in PHP and JScript so far, but I think the
answer to my question should be applicable to any language using the COM
interface.

 

[Tim Williams]

The normal approach is to either substitute the numeric values or to define
the constants in your script.
I'm not aware of any other method (but someone else may be...)

Tim

 

[Tim Williams]

Or this might help:

http://forums.aspfree.com/code-bank-54/constants-for-office-applications-102514.html

Tim

 

[Peter T]

That's not quite VBA but does need tlbinf32.dll to be installed with
Regsrv32. But if it exists in the system this will be easier in Excel.

For early binding (with Intellisense etc) set a reference to "TypeLib
Information" and replace all the "As Object", otherwise late binding should
work fine.

Sub ListConstants()
' requires tlbinf32.dll is registered on the system
Dim a As Long, i As Long
Dim TLIApp As Object 'TLI.TLIApplication
Dim XLInfo As Object 'TLI.TypeLibInfo
Dim c As Object 'ConstantInfo
Dim m As Object 'Members

'Set TLIApp = New TLI.TLIApplication ' early binding
Set TLIApp = CreateObject("TLI.TLIApplication") ' late binding

Set XLInfo = TLIApp.TypeLibInfoFromFile(Application.Path & "\Excel.exe")

For Each c In XLInfo.Constants
a = a + 1
Cells(a, 1) = c.Name
Set m = c.Members
For i = 1 To m.Count
a = a + 1
Cells(a, 2) = m(i).Name
Cells(a, 3) = m(i).Value
Next
Next

End Sub

Keep in mind each Excel version has some new constants.

Regards,
Peter T