Automatic updates on Save

[timspin]

Hi Experts

I have a spreadhseet that utilizes pivit tables, to straight Excell data to charts.

The sheet with charts is saved to webpage.

What I would like is a macro that when a user updates data in the spreadsheet and hits save:

1) All the pivot tables are updated
2) The Charts are updated
3) The Webpage is automaticaly published

I think the charts get updated automaticaly on save, I have also selected the option to update the published web page automaticaly on save. How do I get the pivot tables to update on save?

Thanks for any help.
Regards
Tim

 

[Jerry Sullivan]

Hi Tim,

You could try the code below.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. And, paste the copied code into VBAProject, Microsoft Excel Objects, ThisWorkbook (on the right pane) by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim PC As PivotCache
    For Each PC In ThisWorkbook.PivotCaches
        PC.Refresh
    Next PC
End Sub

Be sure to test that your sequence for these 3 steps is such that the Chart and PivotTables
are updated prior to changes being published to the web page.

 

[timspin]

That works thanks, though now everytime I save my workbook creates a completely new set of charts (perfectly aligned on each other) - you wouldnt notice unless you move one, only to find an identical copy underneath.

If I hit save three time ihave three copies of the chart.

Also have to save as a macro enable workbook, not just click save, isthere anyway around that as well.

Thanks
Tim

 

[Jerry Sullivan]

Regarding the first problem with the PivotCharts, you could try this and see if it works better. I don't work much with PivotCharts, but I'm surprised that refreshing the PivotCaches would create duplicates.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ThisWorkbook.RefreshAll
End Sub

Regarding not using the macro-enabled workbook, I don't think there is a better alternative to automatically do what you describe.

One possible alternative is to setup you datasource as an data connection to an external source. This would allow you to refresh the data every X minutes that you set.
However that leaves a possible gap if your file is saved and published less than 1 minute after your data is changed.

What concern(s) do you have about using a macro-enabled workbook?