How do I gain control of an Excel Spreadsheet embedded in Access form

[feea2006]

I have an embedded pivot table in an access form that needs to be refreshed. I created an event procedure to open the Pivot Table in excel when the form is opened. I have a macro in the excel workbook that that I manually run that refeshes the table and then closes the workbook.

Is there a way in access vba to run the excel macro in the embedded pivot table workbook?

Any help is greatly appreciated. Thanks!

 

[JerryKlmns]

feea2006

Use excel automation (search the forum and the help files) to open the excel file from inside the access mdb and run the macro.

And a question to be raised as a thought.
- Why don't you do all the excel macro processing of data from access (that 's import data to mdb and process)
- Create a query to meet your excel pivot table layout
- Place the query on your form or display the query.

An embeded excel object on an access form, creates a heavy object on access. Bloat of access and resource consumption is the concern, here. Is it just for a nice fomating result?

 

[feea2006]

Thanks JerryKlmns

You raise some valid questions. The pivot table gets its data from an access table. I tried using queries but found it too much to try and get the data to look like the pivot table in the form.

After sepending a whole day yesterday trying to find the answer, I finally got it to work. And rather than sending a command to run a macro in excel, I was able to refresh the table directly from access.

If anyone is interested, here is the code.
Dim pv As Object
Set pv = Me!PivotTable.Object
pv.ActiveSheet.PivotTables("PivotTable1").RefreshTable
Set pv = Nothing

Thanks!