Welcome to SPN

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

Sign Up Now!

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.
     

Share This Page