Updating Excel pivot table from Access form

[paulette33]

I have an Access form that sends data to an Excel spreadsheet with a pivot table. I've written code to update the pivot table based on the the data that updated. The code to update the pivot table is in the pivot table activate event. Here's the problem I'm having, I can't get the table to refresh until I select another page and then select the pivot table. I've add code to the workbook open to activate the pivot table but that doesn't work either. I've even tried to activate the pivot table, another sheet and then the pivot table again. Any ideas?

 

[xlbo]

Activesheet.pivottables.refreshtable

shouldn't even need to go anywhere near the pivot table
HTH
Geoff

 

[paulette33]

Would that also be the case if the data fields in the pivot table are changing? Basically, Access sends data to excel based on criteria/fields selected. Once the information appears in excel, if I put in that code to refresh the table would it also recognize the changed fields? Does that make sense? I'm having a hard time describing what I'm trying to do.

 

[xlbo]

So let me get this straight - you're using a form in access to choose what fields are shown in a pivot table in excel ??

I have to ask - why not just use a crosstab or a pivot table in access ??

Or use the query to bring all data back to excel and choose the fields there ??

Using access to choose the fields for a PT in excel seems like asking for trouble....that is if I understand you right ;-)
Geoff

 

[ETID]

You would not need to export the data to excel, (too many steps)...

Create a live link to the table or query from within excel,

From excel click Data>Get External Data>New Database Query>....the choose msaccess databases* from the list...follow the wizard from there (to select your mdb)
then return your data to excel.

You can opt to use the wizard or MsQuery at this point...
after your comfy with this...I would recommend the MsQuery option.


when data is returned,...you can right click in the data and select properies to refresh on open...fill formulas etc.

Then build your pivot off of the returned data...the only code you would need would be a button to refresh your pivot table after opening the xls

 

[paulette33]

Thank you!! I'll give that a shot!

 

[Susanne46]

I am trying to do the same process now! My end users do not want to go to EDIT-WORKSHEET OBJECT-EDIT-DATA-REFRESH DATA in Access every time they want to view new data. Did you ever resolve the problem? I would be very interested in how you did it.

Thanks so very much,

Susanne