• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

Excel 2003 - deleting connection of pivottable based on external data source

Hello Experts

I have a pivottable based on external data source (Excel 2003).
Can I delete link to this data source from VBA, so that the pivottable can no longer be refreshed?
something like: Sheets(1).QueryTables(1).Delete

thank you very much,
Jarek

0
ja-rek
Asked:
ja-rek
1 Solution
 
jppintoCommented:
ActiveSheet.PivotTables(1).SourceData = ""
0
 
ja-rekAuthor Commented:
jppinto, it doesn't work in my Excel 2003.
0
 
jkasavanCommented:
I am not sure if this works in Excel 2003.

In Excel 2002 assume the source is a range called Data.

Manual steps

Insert -> Name -> Define -> select the name of the range to delete -> select Delete.

Recorded VBA MAcro

Sub NoMorePivot()

      ActiveWorkbook.Names("Data").Delete

End Sub
0
 
Rob HensonIT & Database AssistantCommented:
Select the entire Pivot Table, including the Page fields and do a Copy > Paste Speceial > Values.

This will delete the link and leave a replica of the original pivot but without any of the pivot functionality.

Thanks
Rob H
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now