VBA to change a Pivot table data source to an external file

[Johnny C]

I have a large workbook with many pivot tables linked to 2 external sources. VBA loops through sheets and pivot tables, then uses this code to change it

pt.ChangePivotCache wkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=strROINewSrc)

where pt is the pivot table and
strROINewSrc = 'F:\finance\Report 22\MUEIRE\03 Mar 22\Sales\[EIRESales (Mar - 22).xlsx]!Sales Datasheet'!$A:$AX"
strROINewSrc was created using GetOpenFilename so the file s there and it does have a tab called Sales Datasheet

But I get an error that it can't open the pivot table source.

yet I can go and manually change it on the worksheet so the new source file doesn't need to be open.

what am I doing wrong?

 

[Domenic]

It looks like you missing an opening quote, and you have an extra exclamation mark after the workbook name but before the sheet name. So it looks like it should be . . .

strROINewSrc = "'F:\finance\Report 22\MUEIRE\03 Mar 22\Sales\[EIRESales (Mar - 22).xlsx]Sales Datasheet'!$A:$AX"

Hope this helps!

 

[Johnny C]

Cheers. My old eyes can't see these things any more!

 

[Domenic]

Cheers!

 

[Johnny C]

It worked, but now crashes because it runs out of memory. I'll do a new thread.

 

[Johnny C]

Sorted. Answer - don't add a lot of data sources with a million rows when you data sources all have less than 10,000