+ Reply to Thread
Results 1 to 3 of 3

Can't break data connection for pivot whose source range is in another workbook

  1. #1
    Registered User
    Join Date
    09-02-2009
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    77

    Can't break data connection for pivot whose source range is in another workbook

    Hi all,

    Apologies about the title -- couldn't think of one more descriptive that actually fitted in the space available!

    My exact problem is as follows: I am required to email out a pivot table every quarter and the file size must be kept to an absolute minimum. To do this, I have the source data (which is a named range) and the pivot table in separate workbooks. This works fine, however the recipients of the pivot table workbook do not have access to the network share containing the original two files, so I want to avoid confusion by preventing the "Data connections have been disabled" security warning that appears when opening the workbook, and which allows the recipients to attempt to re-establish the connection.

    There is however no entry in either the "connections manager" or the "name manager" so I'm not sure where or even if I can break the connection.

    So far I have tried:
    • Converting the pivot source named range to refer to a static range (was previously dynamic)
    • Deselecting "Save source data with file" from the pivot table options "Data" tab (probably not wholly relevant to the problem but was worth a shot)
    • Deselecting "Refresh data when opening the file" from the pivot table options "Data" tab

    I am not in a position to self-sign a security certificate.
    I do not want to use "paste values" as the pivot table makes extensive use of expand/collapse buttons.

    Any ideas?

    Thanks for reading

  2. #2
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Can't break data connection for pivot whose source range is in another workbook

    I can only think that you could create a recordset from the source workbook and set it to the pivot cache's recordset property so that all the data is contained within the workbook but I do not know if that would be smaller file size than simply including the source data and setting the pivot table to not store the data.

  3. #3
    Registered User
    Join Date
    09-02-2009
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2007
    Posts
    77

    Re: Can't break data connection for pivot whose source range is in another workbook

    Thanks Izandol, I will give that a shot. I was hoping not to have to resort to VBA, but as long as the output book is VBA-free that won't be a problem.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 05-14-2018, 09:16 AM
  2. Pivot table - break connection to data source
    By TheRobsterUK in forum Excel General
    Replies: 8
    Last Post: 04-09-2014, 07:21 AM
  3. [SOLVED] External links break after the source workbook is closed
    By jankee in forum Excel General
    Replies: 6
    Last Post: 05-21-2013, 09:14 AM
  4. Connecting Pivot Table To Source Data From A Different Workbook
    By bcommando17 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-26-2012, 11:11 AM
  5. Replies: 3
    Last Post: 05-24-2011, 05:33 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1