Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

Access-to-Excel Automation

Discussion in 'Information Technology' started by Bob Barnes, Jul 28, 2006.

  1. Bob Barnes

    Bob Barnes
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    Kicking Marketing Automation into High Gear (NewsFactor) Interfaith Dialogues Mar 29, 2005

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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" <BobBarnes@discussions.microsoft.com> wrote in message
    news:A3B5901A-3175-4828-9C67-55EC81E4B43D@microsoft.com...
    > 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
     
  4. Michael H

    Michael H
    Expand Collapse
    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


    "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
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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" <MichaelH@discussions.microsoft.com> wrote in message
    news:0987EFD9-21AE-4AC4-AFA9-F3263BDCC44E@microsoft.com...
    > 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
     
  6. Michael H

    Michael H
    Expand Collapse
    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

    "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" <MichaelH@discussions.microsoft.com> wrote in message
    > news:0987EFD9-21AE-4AC4-AFA9-F3263BDCC44E@microsoft.com...
    > > 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

    >
    >
    >
     
  7. Bob Barnes

    Bob Barnes
    Expand Collapse
    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

    "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" <BobBarnes@discussions.microsoft.com> wrote in message
    > news:A3B5901A-3175-4828-9C67-55EC81E4B43D@microsoft.com...
    > > 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

    >
    >
    >
     
  8. Bob Barnes

    Bob Barnes
    Expand Collapse
    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

    "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" <MichaelH@discussions.microsoft.com> wrote in message
    > > news:0987EFD9-21AE-4AC4-AFA9-F3263BDCC44E@microsoft.com...
    > > > 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

    > >
    > >
    > >
     
  9. aaron.kempf@gmail.com

    aaron.kempf@gmail.com
    Expand Collapse
    Guest

    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.
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page