Exporting data to Excel pivot table from Access

[Pat Dools]

Hello,

Is there a way to take the results of a select query in an Access database
and export those records to the 'Source' tab of an Excel spreadsheet, and
thus over-write the data that was there to provide 'refreshed' data for the
Pivot Table?

I'd like to put this code behind a command button in my Access application.
Is this possible?

 

[JCS]

Hi Pat,

Try: (this is n Excel 2003)
1. Select Data
2. Select External Data
3. Select Import Data
4. In the "Select Data Source" dialog box navigate to the source Access
database and select it.
5. Select your query from the ""Select Tables" dialog box.

You will then be asked where you want your data to reside. My guess is that
you wll be able to record these keystrokes as a macro and then assign it ot a
macro.

HTH

John.

 

[JCS]

Excuse me! I meant assign the macro to an object.

John

 

[JCS]

In Access, you would:
Create a new macro
Select OpenQuery in one Action line. Fill in the text boxes at bottom of
macro for this action.
Select TransferSpreadsheet on the second action line of your macro. Fill in
the text boxes at bottom of macro for this action. The text boxes are
self-explanatory.

HTH,
John