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

Analyzing Subreports in Excel - uses Subtotal routine in Excel

P: 4
I have tried several ways when running a main report with multiple subreports included in Access and then using the Analyze with MS Excel routine. The output within Excel always places the data in a Subtotal format. Does anyone know how to stop this action and simply display the data in Excel?

Thanks. Al
Jan 28 '08 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 15k+
P: 31,754
Well, I can't see how else it would do it to be honest (that would make any sense). However, in Excel, if you select Data / Subtotals... you can clear any subtotals set. You can't expect the data to be in standard data form though if you've analysed it from a report.

For that you could consider exporting the data from whatever query or table the report uses as its data source instead.
Feb 3 '08 #2

P: 4
I would try with the original table, except I am pulling data from multiple tables or queries into their own subreport in order to have all of the information appear on a single tab in Excel.

Here is my second thought...if I was able to open excel programmatically and remove the subtotals, I would be willing to do that. I have gotten as far as opening the excel application and getting the right tab/worksheet to be active and select all cells. I cannot figure out how to call the menu routine under Data/SubTotal to be able to run the Remove button in the Subtotal routine.

Do you have suggestions on the calls in VBA to get excel to remove the subtotals? Here is what I have so far:

Expand|Select|Wrap|Line Numbers
  1. Public Sub FormatOutput()
  3. Dim xlApp As New Excel.Application
  4. Dim xlBook As Excel.Workbook
  5. Dim xlThisSheet As Excel.Worksheet
  6. Dim xlToolsMenu As Excel.CommandBars
  7. Dim xlThisWindow As Variant, xlRange As Variant, xlColumns As Variant
  9.   Set xlBook = xlApp.workbooks.Open("myworkbook.xls")
  10.   Set xlThisSheet = xlBook.Sheets("Test")
  12.     xlThisSheet.Activate
  15.     xlBook.Save
  16.     xlBook.Close (True)
  17.   xlApp.Quit
  18.   Set xlColumns = Nothing
  19.   Set xlRange = Nothing
  20.   Set xlThisSheet = Nothing
  21.   Set xlApp = Nothing
  22.   Set xlBook = Nothing
  24. End Sub
Feb 13 '08 #3

Expert Mod 15k+
P: 31,754
Off the top of my head "No".
One of the nice things about Excel macros though, is that you can record what you do (Tools / Macro / Record New Macro) and this will create VBA code for you on the fly. Use that to find the code to clear your subtotals. It's so much easier to get going with code in Excel for that very reason.
Let me know how you get on.
Feb 14 '08 #4

P: 4
Well believe it or not I was able to answer my own question and found it by accident in the VBA help. There is a RemoveSubtotals routine, but you have to call it correctly with the right object. When I tried this before, I was not using the right call with a range value, but with either cells or the worksheet itself. Now that I tried the code below, it is doing exactly what I want. Now I can employ this in several other extracted subreports from Access to Excel where subtotals were appearing.

Here is the code to make this happen:

Expand|Select|Wrap|Line Numbers
  2.     xlThisSheet.Activate
  3.     xlBook.ActiveSheet.Name = "Sheet1"
  4.     xlThisSheet.Cells.Select
  5.     xlThisSheet.Range("A1:T60").RemoveSubtotal
  6.     xlThisSheet.Range("A1").Select
  7.     xlBook.Save
  8.     xlBook.Close (True)
Feb 14 '08 #5

Expert Mod 15k+
P: 31,754

try the following for more portable code :
Expand|Select|Wrap|Line Numbers
  1. ...
  2.   With xlThisSheet
  3. ' Select the active sheet
  4.     Call .Select
  5. ' Rename to "Sheet1"
  6.     .Name = "Sheet1"
  7. ' Clear subtotals from all your data
  8.     Call .Range("A1", ActiveCell.SpecialCells(xlLastCell)).RemoveSubtotal
  9.     Call .Range("A1").Select
  10.   End With
  11. ' Close and save
  12.   Call xlBook.Close(True)
Feb 14 '08 #6

Post your reply

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