473,320 Members | 1,839 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Analyzing Subreports in Excel - uses Subtotal routine in Excel

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
5 2512
NeoPa
32,556 Expert Mod 16PB
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
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()
  2.  
  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
  8.  
  9.   Set xlBook = xlApp.workbooks.Open("myworkbook.xls")
  10.   Set xlThisSheet = xlBook.Sheets("Test")
  11.  
  12.     xlThisSheet.Activate
  13.     xlThisSheet.cells.select
  14.  
  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
  23.  
  24. End Sub
Feb 13 '08 #3
NeoPa
32,556 Expert Mod 16PB
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
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
  1.  
  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)
  9.  
  10.  
Feb 14 '08 #5
NeoPa
32,556 Expert Mod 16PB
Alan,

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

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

Similar topics

1
by: Norma | last post by:
I have one report that consists of 3 subreports. Each subreport is pulled from a query with date parameters ( Between and ). When I run this report I have to enter the date parameters 3 times....
1
by: Gary Cobden | last post by:
Hi I have a routine that uses VBA to open a hidden occurence of Excel, and do background computations. However, in the event that the routine terminates abnormally, I have not been able to...
1
by: Don Sealer | last post by:
I have a report that includes 5 different subreports. I'd like to be able to open this report using a date function (Start Date and End Date). I'd like all five subreports to show the data from...
1
by: Eduardo | last post by:
I have a question to the following example: http://msdn.microsoft.com/office/understanding/excel/codesamples/default.aspx?pull=/library/en-us/dnexcl2k2/html/odc_xlnet.asp This example uses an...
2
by: Bagpuss | last post by:
Can anyone help me? I am using VB.NET to output data to an Excel 2002 spreadsheet, this works fine. I would like to know how to change the sheet names and output to a named worksheet and...
3
by: | last post by:
Hello, I have a routine in my Windows application that exports the contents of a datagrid to Excel. It is modeled closely after the HowTo example on MSDN: http://tinyurl.com/5g2jm. Depending...
0
by: kieran | last post by:
Hi, I have an 'export to excel' button on my Datagrid. All works well except that on my Datagrid, I have sub total rows. The subtotal rows use colspan so that the subtotals are given above...
8
by: dilipkvarma | last post by:
Hi All, I am trying to create a subtotal in an Excel sheet through Perl script. Some how I am not able to make it work. Here is what I wrote $Sheet->Activate; $Range =...
1
by: jgabrielson | last post by:
Hello All, I have a windows application that exports data from a datagridview into excel. Now this data can change based on what a user does in the datagridview itself. An example would be out of...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.