Hello:

I tried to use the following code but get the error stating:

Code used:

Sub Test()
With ActiveSheet
.Unprotect Password:="test1"
.PivotTables("PivotTable1").PivotCache.Refresh
.Protect Password:="test1"
End With
End Sub

Error recvd.:

Run-time error '1004'. Cannot run MSQUERY.exe.

Can you please advice? I am able to refresh the Pivot Table manually
without any problem - I am also able to refresh it when the spreadsheet is
not password protected. The security for macros is set to low. And I do not
have the "Refresh on Open" option marked.

Thanks.

Forge

"imapilot_152" wrote:

I am embarrassed it is so easy. I bow to your problem-solving skills.
Thank you very much for your rapid (and working) solution!

"Dave Peterson" wrote:

With activesheet
.unprotect password:="topSecret"
.pivottables("pivottable2").pivotcache.refresh
.protect password:="topSecret"
end with



imapilot_152 wrote:

I have a pivot table that is refreshed automatically through a macro. Works
great.
The line of code in the macro that runs the refresh is
ActiveSheet.PivotTables("PivotTable2").PivotCache. Refresh

Now I want to protect the worksheet that the pivot table is on.
When I protect the worksheet and perform the action that launches the macro,
I keep getting an error that indicates
"Run-Time error '1004': That command cannot be performed while a protected
sheet contains another pivot table report based on the same source data"
The pivot table does not update.

I do not have another pivot table on the worksheet. I don't even have
another pivot table in the same file.
The pivot table is based on data on a different worksheet (same workbook
file though) and this worksheet is also protected. (If this matters at all)

I am operating Excel 2003

Any takers?

--

Dave Peterson