Unanswered: Access Report Grouping to Excel Worksheet
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.
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.
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!
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.