PivotCache.Refresh not working Excel 2007

[Straha]

Background on the issue:

I have a pivot table with data coming from an external source. The external source actually references the same workbook that the pivot table is in. The source data is comprised of three dynamic named ranges on three worksheets. The tables are joined via a SQL statement and then used by the pivot table.

The external source/SQL join method is being used because the multiple consolidation ranges option of building the pivot table is limited and does not provide the level of detail necessary.

The three tables can not be combined into one worksheet.

Now the problem:

The pivot table works correctly. Updates to the source data are reflected in the pivot upon manual refresh. However, I am trying to automatically refresh the pivot using the Worksheet.Activate method with this line of code:

ActiveSheet.PivotTables("ptName").PivotCache.Refresh

(This is the line of code provide by VBA when I recorded the macro using the manual refresh.)

I receive an "Application-Defined or Object-Defined Error" when the sheet is activated. The code also fails when used as a stand-alone macro and not in the Worksheet.Activate method. I can manually refresh and update the pivot but not refresh with VBA.

I think the issue centers around the fact that the external data reference is the same workbook. When the external source is another workbook the automatic refresh works and when the source is a table within the workbook (not external) the automatic refresh works.

Has anyone experienced this problem before and/or have any suggestions on how to workaround the issue?

Thanks.

 

[Marcelo Branco]

Straha,

I'm trying to guess (not tested): it seems that you have to update your query (SQL join) because the cache is created from 3 different sources.

M.

 

[Straha]

Marcelo,

Thanks for the reply. I should have mentioned that I have tried to refresh the query before refreshing the pivot cache on Worksheet.Activation with the following code:

ActiveWorkbook.Connections("Query from Excel Files").Refresh

but I receive the same "Application-Defined or Object-Defined Error" message.


Is there another method of updating the query I am not trying?

I am really at a loss as to what the problem is.

Thanks for your suggestion

 

[xenou]

Excel is generally not good at querying itself - various errors/memory leaks are related to this. Simplest is to keep data in one workbook and your analysis, reports or Pivot Table in another.

You can *try* creating the pivot table in a separate book and then copying the worksheet with the PT into the workbook where the data is. I've no idea why but I've heard that this helps prevent memory leaks (I've never tested this myself).

 

[Marcelo Branco]

"I have a pivot table with data coming from an external source. The external source actually references the same workbook that the pivot table is in. The source data is comprised of three dynamic named ranges on three worksheets. The tables are joined via a SQL statement and then used by the pivot table."

Straha,

Sometime time ago i tried, with no success, a query from an external source (another Workbook) with dynamic ranges.

I usually save/close the source workbook, open a new and empty workbook and do the query. But as the source-worbook was closed it could not calculate (update) the dynamic ranges and i got an error.

So, i'm a little bit surprised how you could successfully did a query to a source-workbook with dynamic ranges...

M.

 

[xenou]

If I read the original post correctly, the pivot data and all the data is in the same workbook, hence the dynamic ranges don't present a problem (albeit that there is an issue with refreshing the pivot cache).

 

[Marcelo Branco]

If I read the original post correctly, the pivot data and all the data is in the same workbook, hence the dynamic ranges don't present a problem (albeit that there is an issue with refreshing the pivot cache).

I read this also but i was wondering how to get data from an external source being the data in the same workbook. I didnt know it was possible.

Maybe i have been completely wrong since everytime i have to create a query, i first have to save and close the source workbook and open a new one.

M.

 

[xenou]

Maybe i have been completely wrong since everytime i have to create a query, i first have to save and close the source workbook and open a new one.

This is my preference, but I have heard reports of pivot tables working when querying the same workbook, after following the steps outlined above (create the pivot table in a separate workbook first, then move it into the workbook with the data). One can always experiment - assuming you keep backups so you can cover yourself if something goes wrong.

 

[Marcelo Branco]

Tks Xenou for the information.

I'll give a try on this, but i confess that i'm puzzled because the wizard (From Other Sources>From Microsoft Query) in the second step asks to select a file.

I dont know how to continue...

M.

 

[Straha]

I read this also but i was wondering how to get data from an external source being the data in the same workbook. I didnt know it was possible.
M.

Possible? Yes. Correct way to do it? Maybe not.

Thanks for the discussion on this. I still haven't fixed the issue - I might just have to settle on the manual refresh.

Marcelo - in the second step asking for a file I just selected the file location of the workbook. I then use this modified version of code found here to always update the connection path to the current file location.

Dim strFile As String
Dim strPath As String
Dim strPath2 As String
Dim strConn As String

strPath = ThisWorkbook.Path
strFile = ThisWorkbook.Name

If Right(strPath, 1) <> "\" Then
  strPath = strPath & "\"
End If

strPath2 = Left(strPath, Len(strPath) - 1)

strConn = "ODBC;DSN=Excel Files;DBQ=" & strPath _
  & strFile & ";DefaultDir=" & strPath2 _
  & ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

ActiveSheet.PivotTables("ptName").PivotCache.Connection = strConn

The dynamic named ranges are joined with the following SQL statement

SELECT *
FROM table1 table1
UNION ALL
SELECT *
FROM table2 table2
UNION ALL
SELECT *
FROM table3 table3

This works using the manual refresh but like xenou mentioned I think the fact that I have Excel looking back on itself is causing some problems doing the update automatically.

Thanks for the help.