Excel: Late Binding

F

faberk

I am trying to use late binding techniques to open Excel, load it with data
and format the data. I am using Set xlApp =
CreateObject("Excel.Application") to create the instance of Excel, but having
problems with the excel properties. For example:

..Orientation = xlLandscape
..HorizontalAlignment = xlRight
..HorizontalAlignment = xlLeft

It appears to be the reference of the xl* constants.

How do i deal with this problem?
 
B

Brendan Reynolds

The Excel constants (xlLandscape, xlRight, etc) are defined in the Excel
object library, so when you use late binding, without a reference to the
Excel object library, you can't use the constants, you have to use the
values - or better, create your own constants ...

Public Const glngcLandscape As Long = 2
Public Const glngcRight = -4152
Public Const glngcLeft = -4131

xlApp.Orientation = glngcLandscape
xlApp.HorizontalAlignment = glngcRight
.... etc ...
 
F

faberk

I understand that now. Thank you. I am, however, haveing problems
identifying the numeric valies of these constants i.e. xlRight and xlLeft and
xlLandscape. where/how do i determine what they are?
 
R

Rick Brandt

faberk said:
I understand that now. Thank you. I am, however, haveing problems
identifying the numeric valies of these constants i.e. xlRight and
xlLeft and xlLandscape. where/how do i determine what they are?
Click to expand...

Temporarily restore the Excel reference and then either use the Object
Browser or in the debug window...

?xlRight <Enter>
-4152

?xlLeft <Enter>
-4131

?xlLandscape <Enter>
2
 
B

Brendan Reynolds

As Rick says, you can add a reference to the Excel Object Library, look up
the constants, then remove the reference again. Or just open Excel, then
open the VBA editor from Excel (Tools, Macro, Visual Basic Editor) and look
them up there.
 
F

faberk

Rick, Brendan..thank you gentlemen!


Brendan Reynolds said:
As Rick says, you can add a reference to the Excel Object Library, look up
the constants, then remove the reference again. Or just open Excel, then
open the VBA editor from Excel (Tools, Macro, Visual Basic Editor) and look
them up there.
Click to expand...
 
R

Rich Skruch

"Brendan Reynolds" said:
As Rick says, you can add a reference to the Excel Object Library, look up
the constants, then remove the reference again. Or just open Excel, then
open the VBA editor from Excel (Tools, Macro, Visual Basic Editor) and look
them up there.
Click to expand...
I found this:
"Built-in Constants in Visual Basic for Applications" (WC0993)
http://support.microsoft.com/kb/q112671/

but it only covers Office up to the '97 version.

Rich.
 
Top