Linking Pivot Cache to a Range in External Workbook using VBA

[Grothgar]

Hi All;

First time poster, long time lurker... this place has taught me more about VBA than any book, which is why I am hoping you can help me!

I have created a spreadsheet which uses 4 external sets of data to build 4 different pivot tables. These can be updated manually (obviously) with no issue, but the spreadsheet is for people whose Excel knowledge is less than proficient, and who I would not trust to update them correctly.

So, I thought I could type the address of the data into Excel, Name that cell in VBA, and then tell the Macro to source the data from that file... so all they would have to do is change the file name every month, and everything would be okay.

MONTHP = Sheets("Front Sheet").Range("B10").Value
CQUARTER = Sheets("Front Sheet").Range("B11").Value
NQUARTER = Sheets("Front Sheet").Range("B12").Value
FULLYEAR = Sheets("Front Sheet").Range("B12").Value
'
Application.ScreenUpdating = False
Sheets("Pivot Data").Visible = True
Sheets("Pivot Data").Select
Range("A2").Select
ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
MONTHP & "Sheet1!R1C1:R10000C83", Version:=xlPivotTableVersion12)

That is what I have come up with so far... however, when I try and run this Macro, it comes up with the following

Run-time error '-2147024809 (80070057)';

Cannot open PivotTable source file '\\pdataserv\userdata\D and A\COMMERCIAL REVIEW\FINANCE\FY12\F03\PIVOT DATA\ACT_COMREV_JUNE MTH_FINAL.xlsSheet1'.

So, my issue, mainly, seems to be that I just need it to specify Sheet 1 to look at, rather than make it part of the File Name. Everything else will be fine then... can you help?

 

[Jerry Sullivan]

Hi All;
First time poster, long time lurker... this place has taught me more about VBA than any book, which is why I am hoping you can help me!

Hi Grothgar, Welcome out of the lurking shadows.

If your SourceData file is in the same folder as your active workbook,
you just need to add some [brackets] around the file name.

ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "[" & MONTHP & "]Sheet1!R1C1:R10000C83", Version:=xlPivotTableVersion12)

If your SourceData file is in a different folder than your active workbook,
you'll also need to add the filepath.

 

[Grothgar]

Thanks for that... I don't think I was clear enough in my original post, now that I look back on it

At the beginning of my Macro, I have said that

MONTHP = Sheets("Front Sheet").Range("B10").Value

and in B10, I have the file path that I want it to look at.

Where my Macro is falling down, is that it appears that the Macro is looking for the File Name that I have specified, and then adding Sheet1 onto the end of the File Name, which obviously doesn't exist.

So, I need the Macro to look at the File Name that is specified in Cell B10 (which works when I try and open it up), and then look at Sheet1 in that workbook... then the rest of the Macro should work.

Hmm... I recorded a Macro which was changing Pivot Data, and I think I may have found my issue. The Macro that I recorded looked like this;

ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"P:\D and A\COMMERCIAL REVIEW\FINANCE\FY12\F03\PIVOT DATA\[ACT_COMREV_JUNE MTH_v8.xls]Sheet1!R1C1:R6080C83" _
, Version:=xlPivotTableVersion12)

Now, I think because I have told MONTHP to be an entire file location, rather than just the file name, that might be the reason it is not working.

 

[Grothgar]

I have sorted it!

Turns out it was a combination of the reply that you posted (thank you for that!) and what I just posted... I needed to split out the File Name from the File Location... now I can alter both, and it will pick different data depending on what it finds

Thanks a Lot!

 

[Jerry Sullivan]

Glad to have helped point you in the right direction.