Welcome to SPN

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

Sign Up Now!

Formatting Excel from Access

Discussion in 'Information Technology' started by danijela.simunovic@gmail.com, Nov 1, 2005.

  1. danijela.simunovic@gmail.com

    Guest

    Hi!
    Is there a way to format Excel from Access? I am exporting a table with
    X Columns from Access to Excel and I would like the first row to be
    lets say Italic and bold and the textcolor would be red and all the
    others would be yellow, the fill color would be red and the would be
    font 14. The format would take place only from the first to the last
    column(X) where i have some data and to the last row with data.
    Just tell me how can i select a section of a sheet like A1:D1 and
    format it.
    Thanks!

    Danijela
     
  2. Loading...

    Similar Threads Forum Date
    Canada Sukhdeep Kaur Chohan of Brampton Wins PM Award for Teaching Excellence Breaking News Mar 5, 2014
    India Sikh Regiment's Passion for Excellence Breaking News May 19, 2013
    Leisure Excellence -skills-Attitude Business, Lifestyle & Leisure Feb 14, 2010
    Katha Excellent Katha by Bhai Ram Singh Kathavachak Videos Aug 8, 2009
    [SPN] Celebrating Five Years of Excellence & Learning Announcements Jun 3, 2009

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    If you'd looked back a couple of days, you would have found the following
    response I gave Andrew Gabb in a thread entitled "Manipulating Excel from
    Access":



    Easiest way is to do what it is you want one-time in Excel, recording it as
    a macro.

    Once you're done, look at the VBA code Excel uses, and adapt it for use from
    Access.

    For example, assume you want the first row (the field titles) to be bold,
    and each of the columns to be expanded to be large enough to show all of the
    data. Recording what's necessary in Excel to accomplish this will give the
    following macro:

    Rows("1:1").Select
    Selection.Font.Bold = True
    Columns("A:A").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Columns.AutoFit

    Unfortunately, it's not quite as simple as just plugging that code into
    Access.

    Because Excel assumes that the VBA code is working with Excel objects, it
    can take a few shortcuts with referring to the objects. When you're running
    from inside of Access, you have to be explicit. It's not sufficient, for
    example, to refer to Rows: you need to indicate Rows on which spreadsheet.
    As well, it's not really necessary to create a selection and then refer to
    that selection, the way Excel does: you can combine those two steps into
    one.

    Note, too, that the 3rd line of code about refers to an intrinsic Excel
    constant xlToRight (if you're not that familiar with Excel VBA,
    Selection.End(xlToRight) lets you extend the current selection to the right
    until the last populated cell. In this case, since the selection is a
    column, this means that the resultant range will be all contiguous columns
    that have some data in them.) It you're using Late Binding, Access has no
    idea what the value of that constant is, so it's necessary to provide the
    actual value.

    In the end, the following code will perform the desired formatting.

    With objActiveWkb.Worksheets(intCurrSheet)
    .Rows("1:1").Font.Bold = True
    .Range(.Columns(1), .Columns(1).End(-4161)) _
    .Columns.Autofit
    End With


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


    <danijela.simunovic@gmail.com> wrote in message
    news:1130777441.421211.299930@o13g2000cwo.googlegroups.com...
    > Hi!
    > Is there a way to format Excel from Access? I am exporting a table with
    > X Columns from Access to Excel and I would like the first row to be
    > lets say Italic and bold and the textcolor would be red and all the
    > others would be yellow, the fill color would be red and the would be
    > font 14. The format would take place only from the first to the last
    > column(X) where i have some data and to the last row with data.
    > Just tell me how can i select a section of a sheet like A1:D1 and
    > format it.
    > Thanks!
    >
    > Danijela
    >
     
  4. danijela.simunovic@gmail.com

    Guest

    Thanks! just one question: What do i have to put instead of
    "intCurrSheet" and "objActiveWkb"?
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    The code assumes that you're using Automation along the following lines:

    Dim objActiveWkb As Object
    Dim objXL As Object
    Dim strWkbName As String

    Set objXL = CreateObject("Excel.Application")
    objXL.Application.Workbooks.Open "C:\Data\MyBook.xls"
    Set objActiveWkb = objXL.Application.ActiveWorkbook

    With objActiveWkb.Worksheets(intCurrSheet)

    where intCurrSheet is the number of the worksheet (1 is the first sheet, 2
    is the second sheet, and so on)

    (replace C:\Data\MyBook.xls with the appropriate path to your workbook)

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


    <danijela.simunovic@gmail.com> wrote in message
    news:1130779660.329296.206260@g47g2000cwa.googlegroups.com...
    > Thanks! just one question: What do i have to put instead of
    > "intCurrSheet" and "objActiveWkb"?
    >
     
  6. danijela.simunovic@gmail.com

    Guest

    Thanks! I got the hang of it! Bu still I don't know how to get the
    border color or border thickness and most important fill color!? Can
    you help me out?
    I tried .Range("A1:D20").Fill.Color = Red .... and so on but no luck!
    Thanks in advance!

    Danijela
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Looking in the Excel help file, the Fill property doesn't appear to apply to
    ranges, but is part of Charting.

    If you're trying to set a colour for the interior of the cells in your
    range, you need something like:

    With objActiveWkb.Worksheets(1)
    With .Range("A1:D10").Interior
    .ColorIndex = 3
    .Pattern = 1 ' xlSolid
    .PatternColorIndex = -4105 ' xlAutomatic
    End With
    End With

    Note that as I said before, since my code uses late binding, I can't use the
    intrinsic constants like xlSolid and xlAutomatic Excel uses. Therefore, I'm
    using the numeric values of those constants, but include the name of the
    constant for documentation purposes.

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


    <danijela.simunovic@gmail.com> wrote in message
    news:1130802022.767625.169100@g43g2000cwa.googlegroups.com...
    > Thanks! I got the hang of it! Bu still I don't know how to get the
    > border color or border thickness and most important fill color!? Can
    > you help me out?
    > I tried .Range("A1:D20").Fill.Color = Red .... and so on but no luck!
    > Thanks in advance!
    >
    > Danijela
    >
     
  8. danijela.simunovic@gmail.com

    Guest

    I have no words!!! Thanks!
    I'll try to find how to get border style and color and font style and
    color and if I get stuck i'll post to the news hoping that I'll get an
    answer from you or somebody...
    Thanks again because your answers were very very helpfull!!

    Danijela
     
  9. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    As I said, use the Macro Recorder in Excel as a starting point. At least
    then you'll know the correct objects!

    Good luck.

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


    <danijela.simunovic@gmail.com> wrote in message
    news:1130850200.361569.4200@g43g2000cwa.googlegroups.com...
    > I have no words!!! Thanks!
    > I'll try to find how to get border style and color and font style and
    > color and if I get stuck i'll post to the news hoping that I'll get an
    > answer from you or somebody...
    > Thanks again because your answers were very very helpfull!!
    >
    > Danijela
    >
     

Share This Page