Change database source for pivot table

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

Hi

I've built a pivot table based on an Oracle database.

however I want to change the source database (from test to live) but can't
figure out how to change the source database.

Any ideas?

Cheers

Neil
 
Just answered my own question, in the query go into table definition and
change the database
 
Neil,

I think I've been working on the same problem. I'm connecting to SQL
server - but the following may provide the base for something that will work.

In a new sheet in a new workbook, I filled in the cells as follows:


A B
1 Old Object String DSN=olddb
2 New Object String DSN=newdb




In this workbook, I created the following macro (and ran it with the
workbook I wanted to change open). (Don't ask me to explain - I pilfered the
base of it from somewhere on the net for changing query tables, then spend a
couple of hours trying to work out what pivot table object allowed me to
manipulate a connection).


'------------------------------------------------------------
Sub ChangeConn()

Dim pt As PivotTable
Dim Wsh As Worksheet
Dim Wbk As Workbook

Dim Base

Dim oldString As String
Dim newString As String

Set Base = Range("A1")

oldString = Base.Offset(0, 1).Value 'B1
newString = Base.Offset(1, 1).Value 'B2


For Each Wbk In Workbooks
For Each Wsh In Wbk.Worksheets
For Each pt In Wsh.PivotTables

pt.PivotCache.Connection _
= Replace(pt.PivotCache.Connection, _
oldString, _
newString)
Next pt
Next Wsh
Next Wbk

End Sub
'-------------------------------------------------------------


Hope this helps.


Kim.
 
Back
Top