Refresh pivot - catch 22?

[baldmosher]

I have a problem that is stumping me…

The default address for the data on my PC is \Tom\Docs\Reporting.

I want it to be \*User*\Docs\Reporting.

I’ve tried to update the pivots automatically on open, in ThisWorkbook
module. The code I'm running (below) is lifted straight from the other
modules, so I know it should work. But that's not the problem I don't think.


Public SpecialPathMyDocs As String ' ref GetSpecialFolderMyDocs

Private Sub Workbook_Open()

' set SpecialPathMyDocs as default My Documents folder

Dim WshShell As Object

Set WshShell = CreateObject("WScript.Shell")
SpecialPathMyDocs = WshShell.SpecialFolders("MyDocuments")

'Open folder in Explorer
'MsgBox SpecialPathMyDocs

' set default path for all pivot tables as default MyDocs folder in order to
allow refresh

Sheets("Files Data").Activate

Range("B9").Select

' this is the line that chooses source address
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'" & SpecialPathMyDocs & "\Reporting\[Data UK YTD.xls]UK'!$A:$BC"

With ActiveSheet.PivotTables("PivotTableF1").PivotFields("Category")
.PivotItems("FILES").Visible = True
End With
' refresh pivot
ActiveSheet.PivotTables("PivotTableF1").PivotCache.Refresh

End Sub



The above code results in a runtime error on workbook open: I can’t update
the default pivot data address without first refreshing the pivots (hence the
error).

Here’s the catch 22. I can’t refresh the pivots if the default address isn’t
where the data is stored. Obviously on my PC it's already correct, so the
code works with the refresh command first, but won't work if I move the
report to another PC (as is the intention).

The only way I can think would be to save the data with the pivots, which
therefore negates the need to refresh the data before changing the address in
the above code, but also makes the report file massive. I can then write a
macro to remove the "save data with table" before saving.

Is there a better way around this problem?

 

[baldmosher]

Quick update

If I try to manually update the pivot table address on my PC (using the PT
wizard), it tells me I must refresh the pivot table first. Obviously I can do
that on my PC.

However, if I try to manually update the pivot table address on my
colleague's PC, it lets me do this. This has really confused me. Is it
because I'm changing the data source, and not just specifying the same
location to complete the PT wizard as I do on my PC?

 

[baldmosher]

OK. Have tested more - the above code above DOES work on my colleague's PC.

See the clarification - the problem on my own PC is that I am not changing
the source data location, which causes the error. Of course, the source data
location DOES change on my colleague's PC, so the macro works, no problem.

However, my original question is still valid - is there a way to get this
code to work on my PC, as well as my colleagues'?? Perhaps a condition that
if the source data is not located at \TomDocs\Reporting that it needs to be
changed?