You can manipulate anything in Excel with VBA through Access that you can within Excel itself, if you declare an Excel application object...
Once you've done that, you can just programmatically select the rows you want and use .Selection.Rows.Group in VBA to group the rows.
Here's a snippet of something I recently tacked together for generating Excel documents based on a template (it was easier for me to use a template file than to generate a new Excel workbook for this particular project) -- I know it looks like it could be optimized using a "With," but when I tried doing it that way, it behaved unpredictably for some reason so I was explicit in all my references:
-
Dim strXlsPath As String
-
strXlsPath = Application.CurrentProject.Path & "\Template.xls"
-
-
Set objExcel = CreateObject("Excel.Application")
-
Dim mySheet As Worksheet
-
-
objExcel.Visible = True
-
objExcel.Workbooks.Open(strXlsPath)
-
objExcel.Workbooks("Template.xls").Activate
-
objExcel.Workbooks("Template.xls").Worksheets("Example").Activate
-
objExcel.Workbooks("Template.xls").Worksheets("Example").Select
-
Et cetera; you can refer to the Excel workbook/worksheets that way and do anything you could do from VBA within Excel at that point. The VBA editor should automatically add the reference you need for the version of Office you're using.
HTH
Have an end user who prefers to see everything in Excel, neatly grouped.
I know how to export to Excel - but is there a way to export a query from Access directly into Excel with groupings without having to manually establish groups inside the excel spreadsheet? (ie selecting rows of data groupings and hitting shift-alt-right or selecting the 'group' button)
Example:
Query data looks like
Group1 Info Info Info
Group1 Info Info Info
Group2 Info Info Info
What I would like in Excel is for the first two rows to automatically be 'grouped' with the + box to the left of the rows.
Ideas? Its not a major pain to manually format, I am just opposed to it in principle.
Ha.
Thanks in advance.
1
