Welcome to SPN

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

Sign Up Now!

Manipulating Excel from Access

Discussion in 'Information Technology' started by Andrew Gabb, Oct 30, 2005.

  1. Andrew Gabb

    Andrew Gabb
    Expand Collapse
    Guest

    I want be able to manipulate an Excel file from Access, to add a few
    extra rows and insert header info (title etc.) and to make the
    header row bold.

    What's the easiest way to do this? Any pointers, references? (I
    don't even know where to look in the online help!)

    Assume Access and Excel XP.

    Andrew
    --
    Andrew Gabb
    email: agabb@tpgi.com.au Adelaide, South Australia
    phone: +61 8 8342-1021, fax: +61 8 8269-3280
    -----
     
  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

    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!)



    "Andrew Gabb" <agabb@tpgi.com.au> wrote in message
    news:OvYMoUH3FHA.3036@TK2MSFTNGP15.phx.gbl...
    >I want be able to manipulate an Excel file from Access, to add a few extra
    >rows and insert header info (title etc.) and to make the header row bold.
    >
    > What's the easiest way to do this? Any pointers, references? (I don't even
    > know where to look in the online help!)
    >
    > Assume Access and Excel XP.
    >
    > Andrew
    > --
    > Andrew Gabb
    > email: agabb@tpgi.com.au Adelaide, South Australia
    > phone: +61 8 8342-1021, fax: +61 8 8269-3280
    > -----
     
  4. Andrew Gabb

    Andrew Gabb
    Expand Collapse
    Guest

    Douglas J. Steele wrote:
    > Easiest way is to do what it is you want one-time in Excel, recording it as
    > a macro.

    [etc. - good stuff snipped.]

    Thanks, Doug. That's got me going along fine.

    But where do you find out about the objects you're working with? Do
    you just look at Excel and guess, or is there a reference for the
    automation interface? (I know virtually nothing about Excel VBA or
    object structure, but I'm very comfortable with Access and Word, for
    example.)

    Andrew
    --
    Andrew Gabb
    email: agabb@tpgi.com.au Adelaide, South Australia
    phone: +61 8 8342-1021, fax: +61 8 8269-3280
    -----
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    "Andrew Gabb" <agabb@tpgi.com.au> wrote in message
    news:OlRrKKU3FHA.2816@tk2msftngp13.phx.gbl...
    > Douglas J. Steele wrote:
    > > Easiest way is to do what it is you want one-time in Excel, recording it

    as
    > > a macro.

    > [etc. - good stuff snipped.]
    >
    > Thanks, Doug. That's got me going along fine.
    >
    > But where do you find out about the objects you're working with? Do
    > you just look at Excel and guess, or is there a reference for the
    > automation interface? (I know virtually nothing about Excel VBA or
    > object structure, but I'm very comfortable with Access and Word, for
    > example.)


    It may depend on what version of Excel, but I've found that the Excel help
    file is really good in this regard. Just make sure you're in the VB Editor
    when you go to the Help file.

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

Share This Page