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

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Bob Barnes" <(E-Mail Removed)> wrote in message
news:A3B5901A-3175-4828-9C67-(E-Mail Removed)...
> 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

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


"Bob Barnes" wrote:

> 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

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Michael H" <(E-Mail Removed)> wrote in message
news:0987EFD9-21AE-4AC4-AFA9-(E-Mail Removed)...
> 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
>
>
> "Bob Barnes" wrote:
>
> > 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

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

"Douglas J Steele" wrote:

> Actually, Access will know what the Excel constant xlRight is if you've got
> a reference set to Excel.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Michael H" <(E-Mail Removed)> wrote in message
> news:0987EFD9-21AE-4AC4-AFA9-(E-Mail Removed)...
> > 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
> >
> >
> > "Bob Barnes" wrote:
> >
> > > 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
>
>
>

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

"Douglas J Steele" wrote:

> 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
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Bob Barnes" <(E-Mail Removed)> wrote in message
> news:A3B5901A-3175-4828-9C67-(E-Mail Removed)...
> > 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
>
>
>

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

"Michael H" wrote:

> 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
>
> "Douglas J Steele" wrote:
>
> > Actually, Access will know what the Excel constant xlRight is if you've got
> > a reference set to Excel.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Michael H" <(E-Mail Removed)> wrote in message
> > news:0987EFD9-21AE-4AC4-AFA9-(E-Mail Removed)...
> > > 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
> > >
> > >
> > > "Bob Barnes" wrote:
> > >
> > > > 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
> >
> >
> >

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

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

MDB is a dead end road.