Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing location of pivot table data source... (2000 SR-3)

    Hello everyone...

    Does anyone know how I would change the location of a database that is the source of data for a pivot table?
    If I refresh the data, it says that the data isn't found (at the old location) which is correct... but where do I go to change it to look for the same table, same database name, but at a different path?

    TIA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    Right click on the pivot table
    WIzard
    <back>
    Select the new range
    <next> etc

    Steve

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    I tried that... It keeps giving me a Login window... prompting me for Authorization... and password... The default user is Admin... I ignored that and selected Database and the path, but it never saves it...
    Any idea what's wrong?

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    I've even gone into MS Query and edited the SQL to say the proper path... but it doesn't change on the workbook itself... I saved the query but that doesn't help...

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    Try changing it in the immediate window:
    Alt-F11 to get to VB, Ctrl-G to open immediate window then enter:
    activesheet.pivottables(1).sourcedata = "'Sheet 1'!A1100"

    or if on another workbook:
    activesheet.pivottables(1).sourcedata="'[wkb Name.xls]Sheet 1'!A1100"

    Change Pivot index, workbook, sheetname and range as appropriate. You must have the sheet with the desired pivot table active

    You can use
    ?activesheet.pivottables(1).sourcedata

    to determine what the source data is currently.


    Steve

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    Try if you can do it with my flexfind utility: First time just have it search for a back slash (check the objects checkbox).
    Next time search for the full pat and replace with the new path.

    Download the utility at my website below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    One thing I noticed while playing (that might be related) is that creating reference to a different workbook, does not create a link. My experiments seem to indicate that it might only update when the other workbook is open. If you don't open the other workbook, it will not update.

    So you can't really point it to a different file and expect different results: open the source file, then refresh.

    If you want it to be "live", then LINK in your pivot workbook to the workbook source data (this will update) and then have the pivot point to the data in the workbook that has the pivot. The data will then be "live" and refreshable without having to open the other workbook.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    Thanks Jan... I already have your FlexFind utility... (Thank you very much BTW! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    But it's not finding anything when I search for a back slash... <img src=/S/frown.gif border=0 alt=frown width=15 height=15> ... even with the objects checkbox checked)

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    As I kind of implied in my other post, I don't think the path is even stored with the pivot only the workbook name.

    Steve

  10. #10
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    Thanks Steve... I really appreciate the help, but all I get is "DataType Mismatch" error... even when I just put... ?activesheet.pivottables(1).sourcedata ...in the Immediate window... I've checked out the object browser and tried to use the pivottable object, pivottables collection, pivotcache... etc... I'm not having any luck...
    The only thing I can get to return anything is this statement... It actually finds that there is a pivottable...

    ?Worksheets("BOC SUMM").pivottables.count
    1

    Lot's of good that does me huh?... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  11. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    Sorry Steve... Didn't get that far... I was playing with code... lol

  12. #12
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    The datasource is a table in an Access 97 database... The only thing that has changed is the path...
    I can change the path in the SQL statement in MS Query... but it doesn't save it... I'll try with the database open and see what happens...
    I've started trying to recreate the table from scratch... but what a pain this is... <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    Does:
    ?Worksheets("BOC SUMM").pivottables(1).sourcedata

    do anything?

    Steve

  14. #14
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    Just gives me... Run-time error 13 - Type Mismatch...

    Well I have it fixed now... Don't ask me exactly how... <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

    Here's what I did... Copied the worksheet... Deleted the pivot table on the new sheet... Used the wizard to create a new pivot table with the new source... Started playing with formating and totals and things... Refreshed the data... etc.... (all on the new sheet)

    But check this out.... On a whim I decided to try refresh the data on the old worksheet... and instead of the error about not finding the external database, the data updated from the location specified on the new sheet... Go figure?!?! ...I've deleted my copied sheet and left the old one (since it's in the right format and sort) ... and it's fine... ?!?!?!
    It drives me batty when I fix things and I don't know how I did it... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Anyway... Thanks for all of your help Steve... <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  15. #15
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing location of pivot table data source... (2000 SR-3)

    Forget my last post... I went back into the xls file...and guess what?... I'm getting the error about the old path again... WTH is going on?????
    There's no way this should be this difficult...
    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

Page 1 of 2 12 LastLast

Posting Permissions

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