Link to home
Start Free TrialLog in
Avatar of ja-rek
ja-rek

asked on

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

Avatar of jppinto
jppinto
Flag of Portugal image

ActiveSheet.PivotTables(1).SourceData = ""
Avatar of ja-rek
ja-rek

ASKER

jppinto, it doesn't work in my Excel 2003.
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
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial