Late binding Excel - xlCenter problem 1

[Andrzejek]

I have this piece of code in VB 6 app:

Dim xlApp
Dim xlBook
Dim xlSheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.ActiveSheet

With xlApp
    .Visible = True
    .Range("F:F,I:I,L:L").NumberFormat = "m/d/yyyy"
    .Range("J:J,M:M").NumberFormat = "$#,##0.00"
    .Columns("N:N").NumberFormat = "$#,##0"
    .Range("D:D,G:G,H:H,K:K").HorizontalAlignment = [highlight]xlCenter[/highlight]
End With
...

As you can see I am using late binding of Excel. Everything works OK except of the line with [tt]xlCenter[/tt], it gives me either "Variable not defined" error (well, I can see why, xlCenter is not defined), if put a dot in front of it, I get "Object doesn't suport this property or method" error. I tried [tt].Constants.xlCenter[/tt]

If I use early binding (use reference to Excel in VB) it works fine.

How can I allign columns in Excel with late binding?

Have fun.

---- Andy

 

[HughLerwill]

Some options;

1. Put back the reference - put a breakpoint on the line including xlCenter - Run the code - Examine the value of xlCenter - Replace xlCentre with the literal value observed or set up an xlCenter Constant representing the value elsewhere in your code.

2. Just put back the reference and leave it there. The app will stay late bound and compatible with any version of xl PROVIDING the Constant name in question is a valid one in the host version of xl. So beware doing this when developing on a latest version of xl and a Constant name which is new to that version.

 

[HughLerwill]

Correction ..

1. Put back the reference - put a breakpoint on the line including xlCenter - Run the code - Examine the value of xlCenter - Replace xlCentre with the literal value observed or set up an xlCenter Constant representing the value elsewhere in your code. And then remove the Reference.

 

[HughLerwill]

Trusting you would Object to Variants

Dim xlApp as Object
Dim xlBook as Object
Dim xlSheet as Object

may be preferable...

 

[Andrzejek]

Thsnk HughLerwill,

This is what I did and it does work fine:

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.ActiveSheet

With xlApp
    .Visible = True
    .Range("F:F,I:I,L:L").NumberFormat = "m/d/yyyy"
    .Range("J:J,M:M").NumberFormat = "$#,##0.00"
    .Columns("N:N").NumberFormat = "$#,##0"
    .Range("D:D,G:G,H:H,K:K").HorizontalAlignment = [blue]-4108[/blue]
End With
...

Have fun.

---- Andy

 

[HughLerwill]

You could manage without all that Diming and Seting and do;

Constant xlCenter = -4108

Private Sub Command1_Click()

With CreateObject("Excel.Application")
.Visible = True
With .Workbooks.Add.ActiveSheet
.Range("F:F,I:I,L:L").Value = Now()
.Range("F:F,I:I,L:L").NumberFormat = "m/d/yyyy"
.Range("J:J,M:M").NumberFormat = "$#,##0.00"
.Columns("N:N").NumberFormat = "$#,##0"
.Range("D,G:G,H:H,K:K").HorizontalAlignment = xlCenter
End With
End With

End Sub

 

[Andrzejek]

Thank you, that's good to know.

Yes, I would normally use [tt]Const[/tt] and even [tt]With[/tt] (Excel), but that was just a simple one-time shot report where I decided to try to do it with late binding. I use early binding all the time and it works OK, but have to challenge myself from time to time. Plus, I refer to the Excel object later in the code where I used ADODB recordset, and since I use the rst a lot, it was better for me to use [tt]With rst[/tt] statement instead of [tt]With xlApp[/tt] (if that makes any sense)


Have fun.

---- Andy