Exporting Query in both Pivot Table and Datasheet view

[Jmoore86]

Morning all - hopefully this will be a nice easy one for you!

I've got a query that can be viewed as a Pivot Table within Access - my users are now asking to be able to export this to an excel file (I can do this using a macro no problem) but they want to have the pivot table on one sheet and the datasheet view in another. Is this possible at all? I reckon i'll have to use some form of VBA wizzardry, but thought i'd check out here too!



Kind Regards,
Jon

[stmoong]

Well, it is possible using VBA.

Typically, I would create an instance of the Excel application object, and then create the workbook and the worksheets required.

One worksheet will be the data in tabular form.

The other worksheet will be the Excel pivot table referencing to the data worksheet. Use the Excel macro recorder to help you with how to generate the Excel pivot table using code.

[Jmoore86]

Thanks for your fast response Stan!

My issue is, I don't want to have to "hand-hold" my users too much and I want to automate this as much as humanly possible... i'd rather they pressed a magic button and a new excel file appeared on a shared drive. If I give them anything to do, they tend not to follow instructions!

Thanks again,
Jon

[stmoong]

What I described is the way it is done in VBA code with a click of the button.

You will have to write the code to do so.

The macro recorder part is to give you reference to the code to generate the pivot table.

[Jmoore86]

Oops - I seem to have skim-read your reply! Just had a closer look, thanks for that I'll start coding!

Thanks again,

[stmoong]

You're welcome!