source data for pivot

[ericlch16]

option 1
sourcedatastring = '\\myunc\_WT_.xls'!$A$1:$CI$65536

option 2
sourcedatastring = 'L:\_WT_.xls'!$A$1:$CI$65536

using VBA i do this:-

pvtTable.SourceData = sourcedatastring


only option 2 works.. when i put a UNC drive as a sourcedata the pivot does not like it.. why? is there anyway i can solve that?

I need to put a UNC location as source data in pivottable in VBA but how?

any help?

 

[Jerry Sullivan]

If you put the book name in brackets and add the sheet name it seems to work. (at least in xl2007)

sourcedatastring = '\\myunc\[_WT_.xls]Sheet1'!$A$1:$CI$65536

Even though the drive letter syntax allows you to omit the sheet name, it's probably best to be explicit in your VBA code.