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

Code for query to "Export to Excel" as pivot

P: n/a
Is there a way to code the button that's available in the query
window--microsoft excel icon that exports to excel. I know
transferspreadsheet will do this---but I want the query, which is in a
pivot table view, to be exported as a pivot, not just a data list.

Does this code exist? Is it transferspreadsheet but with a twist?
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
If you want an Excel Pivot table as your end result, forget it!

I suggest trying this:

1) Get back to the base query (forget the crosstab in Access)

2) Export it to Excel

3) In that Excel file, turn on the macro recorder, and record yourself making
the pivot table

4) Use the code created in step 3 insdie of an Access module, using
CreateObject("Excel.Application") and automation from there.
Nov 13 '05 #2

P: n/a
> Is there a way to code the button that's available in the query
window--microsoft excel icon that exports to excel. I know
transferspreadsheet will do this---but I want the query, which is in a
pivot table view, to be exported as a pivot, not just a data list.
Sure! A 'crosstab' in Access is a 'pivot table' in Excel.
Does this code exist? Is it transferspreadsheet but with a twist?


The code doesn't exist - you'll have to write it. Here's a really
high-level version of what you're going to do:

Use OLE Automation / ActiveX to program Excel through Access VBA
( set up a reference, then dim xl as new excel.application or
something )

Copy all of the data into an Excel worksheet, from the query your
crosstab is BASED ON. Using code, like xl.workbooks(0).cells(y,
x).text = the value from your query.

Then recreate the pivot in Excel, through your code, and save the file
( again through code ).

Otherwise, your options are to take the list/dump you're getting now,
or do your analysis in Access. Writing out your own code really won't
be that hard, though...
Nov 13 '05 #3

P: n/a
Could you post on how this is done with the Pivot table, if you get it
working successfully? I've been trying to do something very similar
and haven't been able to come up with something that I can be happy
with.

I have a docmd.transferspreadsheet command that is exporting to Excel,
the only problem is that the Excel spreadsheet is completely
unformatted and the columns are too small in width (which I can live
with I guess). I recorded a Macro in Excel and tried to use Access to
run it, but some of it works and some of it doesn't.

Dim xls As Object
Set xls = CreateObject("Excel.Application")
xls.Application.Visible = True
xls.Workbooks.Open (path & filename)

'Works
'Freeze Top Pane
xls.Rows("2:2").Select
xls.ActiveWindow.FreezePanes = True

'Doesn't work
'Background color of first row (with fieldnames) to gray
xls.Rows("1:1").Select
xls.Selection.Interior.ColorIndex = 15
xls.Selection.Interior.Pattern = xlSolid
xls.Selection.Interior.PatternColorIndex = xlAutomatic

I'm not sure why the first part works but the 2nd part doesn't. The
code was recorded using the macro recorder in Excel 2000 and copied
and pasted into Access 2000. Any ideas? Thanks.
dc****@aol.comSPNOAM (DCM Fan) wrote in message news:<20***************************@mb-m25.aol.com>...
If you want an Excel Pivot table as your end result, forget it!

I suggest trying this:

1) Get back to the base query (forget the crosstab in Access)

2) Export it to Excel

3) In that Excel file, turn on the macro recorder, and record yourself making
the pivot table

4) Use the code created in step 3 insdie of an Access module, using
CreateObject("Excel.Application") and automation from there.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.