Access-to-Excel Automation

[Guest]

How can I set HorizontalAlignment = xlRight
of a Column or Range thru automation in Access VBA?

Example...this works..
objXLSheet.Columns("F:F").ColumnWidth = 14

This doesn't...
objXLSheet.Range("F1:F500").HorizontalAlignment = xlRight

I am Formatting "Fixed" in an Access Query, but it always left-aligns
in Excel.

Any tips?

TIA - Bob

 

[Douglas J Steele]

Do you have a reference set to Excel, or are you using Late Binding?

See whether it makes a difference if you use the actual value of
xlRight, -4152:

objXLSheet.Range("F1:F500").HorizontalAlignment = -4152

 

[Guest]

Bob,

Access doesn't know about the Excel constant xlRight. If you open the VB
Editor in Excel, and type ? xlRight into the Debug window, you will see
that the constant equals -4152. So, try this:

objXLSheet.Range("F1:F500").HorizontalAlignment = -4152

-Michael

 

[Douglas J Steele]

Actually, Access will know what the Excel constant xlRight is if you've got
a reference set to Excel.

 

[Guest]

Hi Douglas.

Since it wasn't working for Bob, I assumed he didn't have the reference set.
I probably should have asked instead of assuming.

-Michael

 

[Guest]

Doug - THANK you.

I had Office 11.0 Object Library & NOT Excel 11.0 Object Library referenced.

"-4152" Works without Excel 11.0 Object Library referenced...but now I have
it checked. Therefore xlRight works now.

Bob

 

[Guest]

THANKS Michael...I answered Doug (above) w/ this...

Doug - THANK you.

I had Office 11.0 Object Library & NOT Excel 11.0 Object Library referenced.

"-4152" Works without Excel 11.0 Object Library referenced...but now I have
it checked. Therefore xlRight works now.

Bob

 

[aaron.kempf]

i would reccomend not using Excel for anything; it isn't a valid
reporting format.

upgrade your piece of shit mdb app to sql server and use ADP or ADP to
get to the data.

MDB is a dead end road.