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.