Update a linked table at the end of a submit button

[gmazza76]

Good Afternoon All,

I am hoping this is a simple query.
I am using the code below to update a pivot table after information has been added into the pivot tables source in access. Also I have 2 tables on the same sheet that holds data I want to update when the Pivot updates.

Is there anyway I can write code that will also update the tables in the sheet or would I have to write 1 for each table. Sorry to be a pain, but this is new to me and I am unsure the best way to do this. I would use the update function on the table but it doesnt seem to refresh.

Sub Update_Pivot()
Dim xTable As PivotTable
For Each xTable In Application.ActiveSheet.PivotTables
xTable.RefreshTable
Next
End Sub

Many thanks
Gavin

 

[Micron]

Sounds more like an Excel question to me. You may have more luck in that part of this forum, but I'll try to help.
If by table in the spreadsheet you only mean a series of columns and rows, then if it is linked to Access, you'll have to basically invoke the same method in Excel as if you were to use the Refresh All option on the Data tab of the ribbon. If the data is the same layout as I already mentioned, but is not linked, then I believe you need to use the TransferSpreadsheet method to get the new data. That method should work regardless if it is linked or not, but if linked and the sources are not exactly the same and someone uses the ribbon after a transfer, the data will get over-written. If this is an actual table in Excel (something I never knew existed until a few months ago - so I have no experience with other that to say I didn't like it) then I'm guessing the aforementioned methods would still apply, but I'm not sure.