Excel Output Pivot Table ODBC Error

[dazstarr]

Hi

Can someone help me with this please?

I have inherited a access database which has a hyperlink to a excel pivot table on a form.

The database has had to be moved to another network drive. All the queries still work apart from this pivot table. Once you click on the hyperlink - it opens the excel spreadsheet and I get a prompt to enable or disable automatic refresh - i press enable and all looks fine. But if I try to change a parameter value - I get an ODBC error with the old path for the database.

I have been into the linked table manager and refreshed all the tables with the new location. I have searched through all the vba and changed any file paths there but this still doesnt work.

It seems to be embedded into the excel pivot table document.

Does anyone know where this is hidden so I can change it to the new location? Many Thanks
Daz

 

[DCrake]

If my memory serves me correctly if you sit inside the pivot table data on your spreadsheet and select pivot table wizard. when the first screen appears click the Back button and you will be able to re define the source data and the range. Forgive me if I am wrong it's along time since I had to do this.

David

 

[dazstarr]

Hi

Thanks for your reply.

The Excel file has 3 sheets. I am really stuck on this one.

The 1st sheet holds the main pivot table. Sheet 2 holds the info (based on the query from the access database) and for some reason Sheet 3 has a blank pivot table on it.

Not sure why the originator has that.

If I click refresh data on Sheet 1 and 3 - it works fine. When I try on Sheet 2 - i get the same error so it has to be referencing the old filepath on Sheet 2.

I have clicked inside both of the pivot tables and refreshed the data source but it doesnt seem to make a difference.

 

[DCrake]

For purposes of testing why not try to add another worksheet and recreate the link as per you sheet 2 and see what happens

 

[dazstarr]

Thanks for replying.

I think I may give it another go.

I have since heard the original developer is back in tomorrow so I might have been let off the hook!

I am still going to try it though!

I will give it a try and post back this afternoon.

Cheers

 

[chergh]

Try this code to see if it helps:

Sub blah()

Dim pt As PivotTable
Dim ptArr As Variant

For Each pt In Workbooks("BookName.xls").Worksheets("Sheet2").PivotTables

ptArr = pt.SourceData

For i = LBound(ptArr) To UBound(ptArr)
    Debug.Print ptArr(i)
Next i

Next pt

End Sub

This should provide you with the info on where the pivottable is getting its data from. You might need to tweak the code a bit depending on what the actual source is, have a look at the help for the sourcedata porperty or post back here.

 

[dazstarr]

Hi

Please can you tell me where to place this code?

Many Thanks
Daz

 

[chergh]

Press alt-F11 then on the left hand side of the screen should be a window and there should be some icons. Expand the one that says VBAProject(Bookname.xls), then expand "microsoft excel objects" then double click on "thisworkbook" and paste the code in there.

 

[dazstarr]

Hi Thanks for replying.

I have done this. I executed it under the Macro's menu but nothing happened.

Apologies but I am a noob with Excel VBA.

Thanks
Daz

 

[dazstarr]

Hi

I have sorted this now....

I downloaded an add in from

http://www.contextures.com/xlfaqPivot.html#FindSource

which shows you the data source and also lets you edit it.

All I did was run the add in and change the file path and it's all sorted now. Works fine.

Thanks for all your help.

 

[chergh]

The code I provided will print the stuff out to the immediate window (ctrl-g if it isn't open) but you've sorted your problem now so no need for it.

 

[dazstarr]

Thanks for all your help anyway.

Daz