By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,472 Members | 2,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,472 IT Pros & Developers. It's quick & easy.

Exporting from Access to Excel with Groups

P: 1
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.
Dec 21 '07 #1
Share this Question
Share on Google+
1 Reply


brightshadow
P: 10
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:

Expand|Select|Wrap|Line Numbers
  1. Dim strXlsPath As String
  2. strXlsPath = Application.CurrentProject.Path & "\Template.xls"
  3.  
  4. Set objExcel = CreateObject("Excel.Application")
  5. Dim mySheet As Worksheet
  6.  
  7. objExcel.Visible = True
  8. objExcel.Workbooks.Open(strXlsPath)
  9. objExcel.Workbooks("Template.xls").Activate
  10. objExcel.Workbooks("Template.xls").Worksheets("Example").Activate
  11. objExcel.Workbooks("Template.xls").Worksheets("Example").Select
  12.  
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.
Dec 21 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.