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

Posted on 2011-10-27
Last Modified: 2012-05-12
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,

Question by:ja-rek
    LVL 33

    Expert Comment

    ActiveSheet.PivotTables(1).SourceData = ""
    LVL 1

    Author Comment

    jppinto, it doesn't work in my Excel 2003.
    LVL 2

    Expert Comment

    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()


    End Sub
    LVL 31

    Accepted Solution

    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.

    Rob H

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    Title # Comments Views Activity
    Excel 2007 problem 2 22
    Running Out of IP Addresses 9 94
    52/53 week year end date formula 5 33
    Import csv files to MS SQL 5 35
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now