Search and replace PivotTable sources

[/\/\o\/\/]

I (the customer) have a lot of Excel sheets linking to other excel sheets for
pivottables.

Now we did a migration of all the data to another server,

And now I need to change all the links in all the excel sheets.
If I do a select, I can change the property I think by starting the
PivotTableWizard and then changing it (server -> server2)
But as there are realy a lot of XLS files (100 + ) and links (1000 +) to
replace, I'm looking for a script to list all pivots (and other links) in all
excel sheets and replace them by a new value.

I recorded a bit of this :

Range("E20").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'\\Server\projects$\[file.xls]NNInt_Euro'!R1C1:R1000C151"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Customer", _
"Type", "Region", "Country"), ColumnFields:="Data"

but this does not show how I can find them all, we got a big problem here as
this sheets are very important, and can not used at the moment, and ofcourse
this needts to be fixed ASAP,

so any help on how to do (script ) this would be apriciated.

Greetings /\/\o\/\/

 

[Debra Dalgleish]

Ron Coderre has a pivot table add-in that lets you view and edit the
connection string and command text:

http://www.contextures.com/xlPivotPlay01.html

I (the customer) have a lot of Excel sheets linking to other excel sheets for
pivottables.

Now we did a migration of all the data to another server,

And now I need to change all the links in all the excel sheets.
If I do a select, I can change the property I think by starting the
PivotTableWizard and then changing it (server -> server2)
But as there are realy a lot of XLS files (100 + ) and links (1000 +) to
replace, I'm looking for a script to list all pivots (and other links) in all
excel sheets and replace them by a new value.

I recorded a bit of this :

Range("E20").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'\\Server\projects$\[file.xls]NNInt_Euro'!R1C1:R1000C151"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Customer", _
"Type", "Region", "Country"), ColumnFields:="Data"

but this does not show how I can find them all, we got a big problem here as
this sheets are very important, and can not used at the moment, and ofcourse
this needts to be fixed ASAP,

so any help on how to do (script ) this would be apriciated.

Greetings /\/\o\/\/

 

[/\/\o\/\/ [MVP]]

thx, I will look at it tomorrow at work,
Idid not see how to make bulk changes with it,
for now also a find replace with a hex edit seems to work, but we are still
testing with it,

thanks for the tip, this can help us with the users that have a smaller
number of links to help them out.

Greetings /\/\o\/\/

Ron Coderre has a pivot table add-in that lets you view and edit the
connection string and command text:

http://www.contextures.com/xlPivotPlay01.html

I (the customer) have a lot of Excel sheets linking to other excel sheets for
pivottables.

Now we did a migration of all the data to another server,

And now I need to change all the links in all the excel sheets.
If I do a select, I can change the property I think by starting the
PivotTableWizard and then changing it (server -> server2)
But as there are realy a lot of XLS files (100 + ) and links (1000 +) to
replace, I'm looking for a script to list all pivots (and other links) in all
excel sheets and replace them by a new value.

I recorded a bit of this :

Range("E20").Select
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'\\Server\projects$\[file.xls]NNInt_Euro'!R1C1:R1000C151"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields
RowFields:=Array("Customer", _
"Type", "Region", "Country"), ColumnFields:="Data"

but this does not show how I can find them all, we got a big problem here as
this sheets are very important, and can not used at the moment, and ofcourse
this needts to be fixed ASAP,

so any help on how to do (script ) this would be apriciated.

Greetings /\/\o\/\/