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
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.
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: - Public Sub FormatOutput()
-
-
Dim xlApp As New Excel.Application
-
Dim xlBook As Excel.Workbook
-
Dim xlThisSheet As Excel.Worksheet
-
Dim xlToolsMenu As Excel.CommandBars
-
Dim xlThisWindow As Variant, xlRange As Variant, xlColumns As Variant
-
-
Set xlBook = xlApp.workbooks.Open("myworkbook.xls")
-
Set xlThisSheet = xlBook.Sheets("Test")
-
-
xlThisSheet.Activate
-
xlThisSheet.cells.select
-
-
xlBook.Save
-
xlBook.Close (True)
-
xlApp.Quit
-
Set xlColumns = Nothing
-
Set xlRange = Nothing
-
Set xlThisSheet = Nothing
-
Set xlApp = Nothing
-
Set xlBook = Nothing
-
-
End Sub
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.
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: -
-
xlThisSheet.Activate
-
xlBook.ActiveSheet.Name = "Sheet1"
-
xlThisSheet.Cells.Select
-
xlThisSheet.Range("A1:T60").RemoveSubtotal
-
xlThisSheet.Range("A1").Select
-
xlBook.Save
-
xlBook.Close (True)
-
-
NeoPa 32,556
Expert Mod 16PB
Alan,
try the following for more portable code : - ...
-
With xlThisSheet
-
' Select the active sheet
-
Call .Select
-
' Rename to "Sheet1"
-
.Name = "Sheet1"
-
' Clear subtotals from all your data
-
Call .Range("A1", ActiveCell.SpecialCells(xlLastCell)).RemoveSubtotal
-
Call .Range("A1").Select
-
End With
-
' Close and save
-
Call xlBook.Close(True)
Sign in to post your reply or Sign up for a free account.
Similar topics
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....
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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 =...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
| |