Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    populating Excel work book (Access 2003 SP2)

    I have a task to populate an excel sheet from Access with a variable number of columns. i think i can update the columns as i have done this before.
    My problem is to create a Total column which is a sum of all columns to the left of it or a calculation based upon previous rows. This totals column would be generated to the right of the variable number of data columns, but i would need to make this column with all its formulas on the fly.

    Can anybody see a problem if i was to make the totals column the B column and the data columns to the right of that. in this way i don't have to worry about generating any formulas for the totals column.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: populating Excel work book (Access 2003 SP2)

    That should be OK, if the user can live with that.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populating Excel work book (Access 2003 SP2)

    Can you send a DAO recordset to the Excel spreadsheet? If you do it that way, you can capture the right-most column by virtue of the recordset.fields.count, and then do the horrid RC referencing and formula pasting after that for summaries (which you can tell I'm not comfortable with, but did kludge together something that I would paste up to you if you wanted in addition to this). Here's my code that just pastes a recordset sent from Access in the call to Excel. It first populates the column headers from the field names:

    Dim icols As Integer
    Rows("2:2").Select 'and do formatting to this row for the headers, if you like
    For icols = 0 To incomingSet.Fields.Count - 1
    Cells(2, icols + 1).Value = incomingSet.Fields(icols).Name
    Next
    Range("A3").CopyFromRecordset incomingSet 'voila

    thx
    Other Pat

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: populating Excel work book (Access 2003 SP2)

    Think i might do that it this way, could ba a lot easier, but then i don't know Excel.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: populating Excel work book (Access 2003 SP2)

    Thanks Other Pat, but is this code in Access or Excel?

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populating Excel work book (Access 2003 SP2)

    It is in Excel. You can send -- well, you know, I've done a couple of different things lately, and I don't know if I actually passed the record set from Access to Excel as a parameter, but I know that an Excel function can receive a recordset, so I don't see why not. Let me check what I did in a couple of instances, and I'll return shortly ...
    thx
    Other Pat

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: populating Excel work book (Access 2003 SP2)

    I want to keep all control of this in Access.

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populating Excel work book (Access 2003 SP2)

    Well, you can do everything within Access, but you would have to include the Word library as a reference, in order to get all the Excel functions and run them while within Access. The project I was looking at for the code actually was entirely in Excel, and I got my recordset from a connection to a SQL server. Two other projects are merge files from Access, and neither actually involve a recordset, although I don't see why one cant' be sent. Both call a macro in the word merge document. In one I sent a SQL string -- here is my call in my Access project: "appword.Application.Run ("GoMrg"), sqlStmt." You could do it any number of ways.
    thx

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: populating Excel work book (Access 2003 SP2)

    Thanks Pat, i will check that out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •