Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Location
    Dubai, UAE
    Posts
    4

    Unanswered: Access Report Grouping to Excel Worksheet

    Hi,

    I've been researching this for a while now, and haven't yet found the answer. Any help you can provide, or material to research would be very useful.

    I'm trying to export an Access 2002 report to Excel. The Access report has groups, and I would like the first group to be displayed on a new worksheet in Excel. Currently, the Access Grouping comes through as Excel grouping (automated feature), and I'm not sure how to get around that.


    Thanks in advance for any help...

  2. #2
    Join Date
    Jan 2002
    Location
    At the Edge of America!
    Posts
    55

    RE: Access Report Grouping to Excel Worksheet

    You will need to do this in a function or a sub routine. I have done something like it for a web page not for Excel but it should be just about the same. I dont know the code but I have the concept for you.


    In your module go through each record one at a time and determine what your group name is. Set a Var to that value and then do a check during each loop to see if the var is = or is <> if it is not equal then you would want to start a new worksheet.

    HTH

  3. #3
    Join Date
    Dec 2003
    Location
    Dubai, UAE
    Posts
    4
    Thanks suthngin. I'll give it a try...

  4. #4
    Join Date
    Dec 2003
    Location
    Dubai, UAE
    Posts
    4
    Hi again,

    Here are the problems with exporting Report Group :

    1. If I assign a variant to the group, I have to reformat the recordset as well. Unless there is a better way of doing this, it takes way too long!

    2. The feature in Access reports --> myRpt.Section(5) where 5 is the acGroupLevel1Header does not allow me to see the name or caption to do with the recordset value. Is there some other property that is out there that will let me get the value of the Group to compare to the recordset value?

    Currently, if Access groups exist in a report, the groups are outlined in Excel when the report is exported. Is there a way of changing the exported outline to a new worksheet?

    Any help or more suggestions would be appreciated!

    Thanks!

  5. #5
    Join Date
    Jan 2002
    Location
    At the Edge of America!
    Posts
    55

    Not reports... query

    I would not use the reports in access I would just use a query that has all of your reporting data in it. Loop through each record and check the "Group Column" ie Food Type or Month. In the first time through the loop assign a varible to a value of 0.

    1-Check to see if the varible equals the group field. (first time through it will not)
    2-If it does not then send the command to excel to create a worksheet with the name of the group. You will also start creating the first row of your excel spreadsheet in your new worksheet. Move to the next row. You might want to call another procedure to create your heading in excel for each group.

    Tell your code to set the value of your varible equal to the group field and then move to the next record and restart your loop.

    3- If your group field equals your varible then you know that this next record still belongs to the same group. So just insert the row in access and set your varible again equal to the group field and then move to the next record.

    4-Do this untill EOF

    Hope you understand what I am trying to explain

    Thanks


  6. #6
    Join Date
    Dec 2003
    Location
    Dubai, UAE
    Posts
    4
    I got it to work (picture me doing the 'Dance of Joy' around the office...)

    Thanks for your help suthngin (sp?).

    I've attached the code in case others are interested....
    Last edited by mesmarr; 01-20-04 at 04:03.

  7. #7
    Join Date
    Jan 2002
    Location
    At the Edge of America!
    Posts
    55
    Happy to help!

  8. #8
    Join Date
    Oct 2003
    Posts
    2

    Access Report Grouping to Excel Worksheet

    Quote Originally Posted by mesmarr View Post
    I've attached the code in case others are interested....
    mesmarr:
    I know it's 11 years later, but I'm still interested...

    The code is no longer attached to this thread. Any chance you can reattach it?

    Thanks!

Posting Permissions

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