Edit links to external worksheet

[myobreportguru]

Hi
I'm using excel 2010 and I've created a series of pivot tables in a new workbook from another workbook. I want to move these these worksheets to another computer but because they are now in different folders I can't refresh the data without editing the links. However my edit links function is grayed out and the only other way I can see is to edit all of them individually through connections or properties. Is there a way of making the edit links function work or another fast way of updating links? Any help appreciated. Thanks.

[:) Sixthsense :)]

I am confused whether you are talking about Pivot Table Data Source (Alt+J+T+I+D) or Edit Links (Alt+E+K)?

[myobreportguru]

Ok the edit links I'm referring to is under the connections on the data menu. The pivot tables were created by get external data from other sources/microsoft query/excel files - then selecting the file and worksheet and columns. I'm hoping that edit links will do a fast reconnect to my data workbook.

[:) Sixthsense :)]

Not sure what your query is?

[myobreportguru]

my edit links is grayed out. I can't work out why.

[:) Sixthsense :)]

Please show a screenshot if possible

[myobreportguru]

Here's a screenshot

[:) Sixthsense :)]

Thanks for the screen shot

Edit Links button will be accessible when you have FORMULA REFERENCES to other WORKBOOKS

[myobreportguru]

so what you're saying is "Get External Data" using microsoft query to get data from another excel workbook might not be treated as external links.

[:) Sixthsense :)]

Yes exactly, only formula's to other workbooks alone shown in Edit Links and Get External Data is related with Pivot.

[myobreportguru]

Thanks now I understand. One more question though, if I'm using get external data to create pivot tables etc, is there a fast way to update the links when I move the source workbook to another folder. At the moment the only way I can see is manually going through each connection and changing the path to the new location.

[:) Sixthsense :)]

Using VBA code you can do it in a single click

[myobreportguru]

I would really appreciate some help on this.

[:) Sixthsense :)]

Surely but please explain whether it needs to be changed (External Location Reference) to all the pivot Table's in the active workbook or activesheet pivots?

Whether it needs to be applied to specific pivots based on any identification?

[myobreportguru]

exactly right, external location reference for all pivot tables in the active workbook is what needs to be changed. Everything else remains the same.

[:) Sixthsense :)]

Thanks for the confirmation but now i am away from office and back to work on monday only. So i will give reply by monday if your query remains unsolved.

[myobreportguru]

Thanks for your recent help on this. Will you be able to help me with VBA code?

[:) Sixthsense :)]

Copy and paste the below code in a Standard module and run it

Please Login or Register  to view this content.

[myobreportguru]

Test.zip

I'm not sure if I have done this correctly. The zip file attachment has 2 files. The report file has the pivot tables and gets its data from the data file. Could you test it please.

[:) Sixthsense :)]

Refer the below link for details:-

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

[myobreportguru]

Macro Error.docx

Thanks again for your help on this. I watched the video the web link and think I have done it correctly but it's still stopping with an error. See the attached error, I'm sorry to keep bothering you. Regards

[myobreportguru]

Run Time Error.docx

I have just up loaded the runtime error as well which may help you.

[:) Sixthsense :)]

Download Pivot Play Plus Addin from the below link and read the contents to know about how to use it
http://www.contextures.com/xlPivotPlayPLUS01.html

[myobreportguru]

Thanks I've dowloaded that and I can now change the source data connection. Appreciate your help.

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback