Copying Pivot Table From Access Into Excel

[cstraim]

Hello All

I have created a few pivot tables in Access that looks great on screen,
but when printed, prints to more than one page. So, I am looking to
copy the entire table into a new excel workbook. I know how to do it
manually (CTRL-A, CTRL-C and then CTRL-V in Excel), however, I would
like to get that same procedure done automatically via VBA for anyone
looking to print the report.

Can one of you tell me how to create the code that will select the
entire open pivot table in Access, copy it, and then open a new
workbook in excel and paste it in. I will assign it to a "print"
button for people to print it.

I cannot export it to excel, as I am using date functions in Access
that are not availbale in Excel pivot tables) (like date by week). If
I export it from Access to excel, it get's rid of the week column

Thanks much for your help

Cory

 

[Stefan Hoffmann]

hi,

Can one of you tell me how to create the code that will select the
entire open pivot table in Access, copy it, and then open a new
workbook in excel and paste it in. I will assign it to a "print"
button for people to print it.

You can only copy the visible result of your Access pivot, the data, not
the formulas behind.

I cannot export it to excel, as I am using date functions in Access
that are not availbale in Excel pivot tables) (like date by week). If
I export it from Access to excel, it get's rid of the week column

What have you already tried to do:

- DoCmd.TransferSpreadSheet?
- Excel automation?
- Jet export?


mfG
--> stefan <--

 

[cstraim]

Well i can't transfer the spreadsheet becuase the functionality that
access offers would be lost (like date by week). I unfortunately do
not have enough knowldge of VBA to know how to copy data from a pivot
table in access and paste it into excel as values.

 

[Stefan Hoffmann]

hi,

Well i can't transfer the spreadsheet becuase the functionality that
access offers would be lost (like date by week).

So why do you want to transfer it? I don't get the point.



mfG
--> stefan <--