External Data (MSQuery) AND Using and Auto_Open Macro

[Poppy]

This is really two problems: A Pivot Table won't refresh, and an Autpen Macro won't run.

I have a worksheet linked to a table in MS Access. I created it by "Get External Data" - "MSAccess", etc., and ultimately selecting "Update on Open" so that each time the worksheet is opened, the data is refreshed. THIS PART WORKS.

However, I created a Pivot Table based on that worksheet, and although I have checked the box in the Pivot Table "OPTIONS" to "Refresh on Open", it does not refresh. As a work-around, I tried to create an Autpen Macro that has just a couple of lines of code-- to select the worksheet, go to a legitimate cell, and refresh the Pivot Table. The macro doesn't appear to be running when the workbook is opened. But if I run the macro after the worksheet is open, the macro commands run as intended. ANY HELP WOULD BE APPRECIATED. Thank you.

 

[Tommy Bak]

Hi
Your remove the refresh on open on both the query and the pivottabel and then use the auto_open macro to refresh both.
I thing the problem is that your pivottable refreshes before your query, so you will never have new results in the pivottable.

Sub auto_open()
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveSheet.PivotTables("Pivottabel1").RefreshTable
End Sub

regards Tommy

 

[Jack in the UK]

I agree with the post above, pivots are fast ODBC is slow i always tell guys getthe data then minipulte it NOT before or both together


i would have two scipts ...