restore external links to excel pivottables

[tpat]

Hello, i have a number of pivot tables in an excel workbook that connect to a MS Access database.

My intention is to distrubute the dbase and the excel workbook to others who will use both applications from the local drive.

When i move the applications (together) the Pivottable connection to make a link to the dbase table appears to fail.

I am accessing the pivottable fields and items via a listbox facility on another worksheet and vba. When i select an item in the pivottable fails.

Everything works well on my local drive but the problem occurs when i pass this to another colleague for test.

Can anyone help with a way of programmatically determining what current path is to the dbase and if it is not the same as the directory path to the excel workbook then update the dbase connection path?

Hope this all makes sense.

Regards,

Tyrone

[Bob Phillips]

Tyrone,

Should not be a problem changing the link, but the issue is getting that path. Is it okay for you to add an option to your app whereby the users browse for the database, would they know how to, would they know what they are looking for?

[tpat]

thanks,

my instriction is that the workbook and the dbase live togetter in the same directory. So when the excel workbook is opened it will determine the workbok path (thisworkbook.path) and the path to the dbase will be (should be) the same. if the dase base is not found in that directory then and error mrsg would appear telling the user to put the dbase i the same directory.

then i just need to do the ptable external links

Tyrone

[Bob Phillips]

Can you post your current odc file as we will need to update that.

[Bob Phillips]

Oh, and where is that file located on your machine?

[tpat]

the excel work book and MS database is located on my locat drive at C:\Data (say)

Ihave rcreated cthe first of 5 pivottables from the MA access Dbase in a consistent manner the PT1 was created as outlined below.


Sub mk_PT1()
Dim dBASE As Database
Dim ODBC_CONNECT_STRING As String
Dim varsource As Variant
Dim PT As PivotTable
Application.ScreenUpdating = False
ODBC_CONNECT_STRING = "ODBC;" _
& "DBQ=" & ThisWorkbook.Path & Application.PathSeparator & "IPCostSummary_08-09_09-10.mdb;" _
& "Driver={Microsoft Access Driver (*.mdb)};"
'Create array containing connection string and SQL string.
varsource = Array(ODBC_CONNECT_STRING, "SELECT * FROM " & "TBL_TOTAL_IPCOSTSummary")
' Create PivotTable report. Start at cell B8 so there is enough room
' for page fields.
Set PT = PT_GROUP.PivotTableWizard(xlExternal, varsource, _
PT_GROUP.Range("A12"), Tablename:="PT1")

With PT
.PivotFields("Patient_Type").Orientation = xlPageField
.PivotFields("LOS_Type").Orientation = xlPageField
.PivotFields("InlierStatus").Orientation = xlPageField
.PivotFields("vicDRG").Orientation = xlPageField
.PivotFields("finyear").Orientation = xlPageField
.PivotFields("Network").Orientation = xlColumnField


With .CalculatedFields
.Add "*ALLIED", "=" & PT.PivotFields("ALLIED") & "/" & PT.PivotFields("SEPN")
.Add "*ICU_CCU", "=" & PT.PivotFields("ICU_CCU") & "/" & PT.PivotFields("SEPN")
.Add "*EMERGENCY", "=" & PT.PivotFields("EMERGENCY") & "/" & PT.PivotFields("SEPN")
.Add "*IMAGING", "=" & PT.PivotFields("IMAGING") & "/" & PT.PivotFields("SEPN")
.Add "*MEDICAL", "=" & PT.PivotFields("MEDICAL") & "/" & PT.PivotFields("SEPN")
.Add "*NURSING", "=" & PT.PivotFields("NURSING") & "/" & PT.PivotFields("SEPN")
.Add "*PATHOLOGY", "=" & PT.PivotFields("PATHOLOGY") & "/" & PT.PivotFields("SEPN")
.Add "*PHARMACY", "=" & PT.PivotFields("PHARMACY") & "/" & PT.PivotFields("SEPN")
.Add "*THEATRE", "=" & PT.PivotFields("THEATRE") & "/" & PT.PivotFields("SEPN")
.Add "*OTHER", "=" & PT.PivotFields("OTHER") & "/" & PT.PivotFields("SEPN")
.Add "*PROSTHETIC", "=" & PT.PivotFields("PROSTHETIC") & "/" & PT.PivotFields("SEPN")
.Add "*S100", "=" & PT.PivotFields("S100") & "/" & PT.PivotFields("SEPN")
.Add "*PBS", "=" & PT.PivotFields("PBS") & "/" & PT.PivotFields("SEPN")
.Add "*HITH", "=" & PT.PivotFields("HITH") & "/" & PT.PivotFields("SEPN")
.Add "*PNDC", "=" & PT.PivotFields("PNDC") & "/" & PT.PivotFields("SEPN")
.Add "*ATOTAL", "=" & PT.PivotFields("TOTAL") & "/" & PT.PivotFields("SEPN")
End With

.PivotFields("*ALLIED").Orientation = xlDataField
.PivotFields("*ICU_CCU").Orientation = xlDataField
.PivotFields("*EMERGENCY").Orientation = xlDataField
.PivotFields("*IMAGING").Orientation = xlDataField
.PivotFields("*MEDICAL").Orientation = xlDataField
.PivotFields("*NURSING").Orientation = xlDataField
.PivotFields("*PATHOLOGY").Orientation = xlDataField
.PivotFields("*PHARMACY").Orientation = xlDataField
.PivotFields("*THEATRE").Orientation = xlDataField
.PivotFields("*OTHER").Orientation = xlDataField
.PivotFields("*PROSTHETIC").Orientation = xlDataField
.PivotFields("*S100").Orientation = xlDataField
.PivotFields("*PBS").Orientation = xlDataField
.PivotFields("*HITH").Orientation = xlDataField
.PivotFields("*PNDC").Orientation = xlDataField
.PivotFields("*ATOTAL").Orientation = xlDataField

.DataPivotField.Caption = "AVERAGE COST"

With .DataPivotField
.PivotItems("Sum of *ALLIED").Caption = "ALLIED*"
.PivotItems("Sum of *ICU_CCU").Caption = "ICU_CCU*"
.PivotItems("Sum of *Emergency").Caption = "EMERGENCY*"
.PivotItems("Sum of *IMAGING").Caption = "IMAGING*"
.PivotItems("Sum of *MEDICAL").Caption = "MEDICAL*"
.PivotItems("Sum of *NURSING").Caption = "NURSING*"
.PivotItems("Sum of *PATHOLOGY").Caption = "PATHOLOGY*"
.PivotItems("Sum of *PHARMACY").Caption = "PHARMACY*"
.PivotItems("Sum of *THEATRE").Caption = "THEATRE*"
.PivotItems("Sum of *OTHER").Caption = "OTHER*"
.PivotItems("Sum of *PROSTHETIC").Caption = "PROSTHETIC*"
.PivotItems("Sum of *S100").Caption = "S100*"
.PivotItems("Sum of *PBS").Caption = "PBS*"
.PivotItems("Sum of *HITH").Caption = "HITH*"
.PivotItems("Sum of *PNDC").Caption = "PNDC*"
.PivotItems("Sum of *ATOTAL").Caption = "ATOTAL*"
End With
End With
ThisWorkbook.ShowPivotTableFieldList = False
Application.ScreenUpdating = True
End Sub


AS previously outlined is is the odbc_connect_String that needs to be reset when the dbase and worksheet is distributed.

tyrone

[Bob Phillips]

At first glance, that doesn't look as though t needs changing at all. You are setting the DBQ to a file in the same folder as the application workbook. There s a date in the filename, is that the issue, otherwise I cannot see the problem.

[tpat]

is the inference here that if i move the database and exce lfile together to another location on another local drive (C:) then this all should work fine? Thanks very much for your help - i will investigate further.

tyrone

[Bob Phillips]

Yep, that is what it looks like to me.