PIVOT TABLES

akhil8377

Board Regular
Joined
Jul 9, 2002
Messages
63
Where is the information stored along with Pivot Tables for connecting to external data source.

I have lots of Workbooks having pivot tables. Each Pivot table is made from External database- SQL SERVER.

For some reason I had to move my Database from one server to another server. I edited the dsn entry in ODBC configuration pointing to new server, but still excel is trying to look for old database in old server. (Original location).

How can I make excel look for New Server for these Pivot Tables.

I have kept even the DSN name same as original.

Please help since there are approximately 200 queries made and changing all these again would mean lots and lots of pain and time
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi,

This is a bit of a pain to fix, but can be done. First of all, select a cell in one of your 'broken' pivot tables and then run this macro:-

Code:
Sub GetSourceData()
    Dim pvt As PivotTable

    Set pvt = ActiveCell.PivotTable

    Sheets.Add
    Cells(1, 1) = pvt.SourceData(1)
    
End Sub

You should see your source database printed out. Copy what it says into a reply. Then go to a pivot table which looks at the new server (i.e. is working) and run the same macro. Post what it says into your reply.

I should then be able to whip up some code which can go into your workbooks, automatically change the source data.
 

akhil8377

Board Regular
Joined
Jul 9, 2002
Messages
63
Hi,

Here is what i Got-

Broken Table-

DRIVER=SQL Server;SERVER=192.168.1.189;UID=akhilesh;PWD=;APP=????????;WSID=PROD235;DATABASE=PDMIS;QueryLog_On=Yes;StatsLog_On=Yes

Working Table

Description=PDMIS;DRIVER=SQL Server;SERVER=192.168.1.121;UID=akhilesh;PWD=;APP=????????????????????;WSID=AKHILESH;Network=DBMSSOCN

As can be seen the only difference is Server Address.
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hello again,

I apologise but it appears that you can't change this data source without causing Excel to crash. I tried this code:-

Code:
Sub ChangePivotTable()
Dim strFilename As String
Dim pvt As PivotTable, sht As Worksheet

Dim strOldAddress As String, strNewAddress As String

strOldAddress = "192.168.1.189"
strNewAddress = "192.168.1.121"

strFilename = Application.GetOpenFilename("Microsoft Excel Workbooks (*.xls),*.xls", , _
                                "Please select workbook...")
                                
If strFilename = "False" Then Exit Sub

'Open the workbook, search for any pivot tables.  If the pivot table
'has the string 192.168.1.189 in it then replace it with the new address

Workbooks.Open strFilename

For Each sht In ActiveWorkbook.Worksheets
    For Each pvt In sht.PivotTables
        If InStr(1, pvt.SourceData(1), strOldAddress, vbTextCompare) > 0 Then
            pvt.SourceData(1) = Replace(pvt.SourceData(1), strOldAddress, strNewAddress, 1, , vbTextCompare)
        End If
    Next pvt
Next sht



End Sub

But it caused the whole Excel application to crash. I'm out of ideas...

_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-10-30 09:16
 

akhil8377

Board Regular
Joined
Jul 9, 2002
Messages
63

ADVERTISEMENT

Hi,

Yeah this is causing excel to crash.

can we use connection property, or some update property. I found this, which resembles what we have to do

This example supplies new ODBC connection information for the first query table on the first worksheet.

Worksheets(1).QueryTables(1) _
.Connection:="ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;"


Somehow I am unable to put this through.

Can u help.

akhilesh
 

akhil8377

Board Regular
Joined
Jul 9, 2002
Messages
63
It seems that Connection property can be used only for query tables so it wont work with pivot tables.

How can pivot table source data or connection string can be changed.

akhilesh
 

akhil8377

Board Regular
Joined
Jul 9, 2002
Messages
63
Hi DK

I found the solution,u were so near to it. the solution was to use substitute instead of replace. I am giving below two codes, one to list source data and one to change the server. (Got it from Microsoft)


Sub ListSourceData()
'Declare our variables.
Dim newSheet As Worksheet, sdArray As Variant
Dim oldSheet As Worksheet, pt As PivotTable, r As Integer

'Set our variables.
Set oldSheet = ActiveSheet
Set newSheet = ActiveWorkbook.Worksheets.Add

newSheet.Range("A1").Value = oldSheet.Name
r = 3

'Loop through each PivotTable on the active sheet and place its ODBC information on a new sheet.
For Each pt In oldSheet.PivotTables
newSheet.Cells(r, 1).Value = pt.Name
newSheet.Cells(r + 1, 1).Value = pt.PivotCache.Connection
newSheet.Cells(r + 2, 1).Value = pt.PivotCache.Sql
r = r + 4
Next pt
newSheet.Cells.EntireColumn.ColumnWidth = 100
newSheet.Cells.EntireRow.AutoFit
End Sub

Sub ChangeServer()
'Declare our variables.
Dim ptc As PivotCache, oldSrv As String, newSrv As String

'Request the name of the old server/file name.
oldSrv = InputBox("Input the name of the old server or file path as listed in the Pivot Tables SQL string.")

'Request the name of the new server/file name.
newSrv = InputBox("Input the name of the new server or file path which you want the Pivot Table to point to.")

'Replace the ODBC information of whatever PivotTable is currently active.
Set ptc = ActiveCell.PivotTable.PivotCache
ptc.Connection = Application.Substitute(ptc.Connection, oldSrv, newSrv)
'ptc.Sql = Application.Substitute(ptc.Sql, oldSrv, newSrv)
End Sub


Thanks for ur help
 

Forum statistics

Threads
1,144,767
Messages
5,726,177
Members
422,660
Latest member
mrsteele

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top