Updating Multiple Pivot Ranges

Please help:


I have about 330 pivots (yeah, it's a lot) on six different tabs whose
source data (range) is located in two different files.


My Excel crashed and after I accepted the auto-save updates, the range
addresses changed from being located in the same folder (where my
pivots are located, [Filename.xls]Sheet1!$A$1:$ZZ$62000) to being the
entire address (C:\Documents and Settings\user\applications\etc...).


I tried in Edit->Links to 'relink' the correct files; however, when I
re-open the file, it continues to reference the entire address.


How can I effeciently change all of the range addresses?


Thanks,


Joshua

I would have guessed that Edit|Links would have worked. You're sure you saved
after you did that change?

Maybe you can cheat.

Move that file that is pointed to to:
C:\Documents and Settings\user\applications\etc.....

Then open your file--the links will point to the now correct location.

Now open that file in:
C:\Documents and Settings\user\applications\etc....

And save it where you want it to really be.
then close that workbook.

Did it work?


SJJoshua wrote:
>
> Please help:
>
> I have about 330 pivots (yeah, it's a lot) on six different tabs whose
> source data (range) is located in two different files.
>
> My Excel crashed and after I accepted the auto-save updates, the range
> addresses changed from being located in the same folder (where my
> pivots are located, [Filename.xls]Sheet1!$A$1:$ZZ$62000) to being the
> entire address (C:\Documents and Settings\user\applications\etc...).
>
> I tried in Edit->Links to 'relink' the correct files; however, when I
> re-open the file, it continues to reference the entire address.
>
> How can I effeciently change all of the range addresses?
>
> Thanks,
>
> Joshua

--

Dave Peterson

Thanks, Dave,

I tried cheating, whereby I did exactly what you suggested. For my data
that wasn't contained in a pivot, updating the Edit|Links worked fine.
However, the range addresses for the Pivots remained constant.

Is there any way to look at the code of the Pivots to do a 'Find
All/Replace' type thing? I certainly hope that what I'm trying to do is
possible...

Thanks!

"Dave Peterson" wrote:

> I would have guessed that Edit|Links would have worked. You're sure you saved
> after you did that change?
>
> Maybe you can cheat.
>
> Move that file that is pointed to to:
> C:\Documents and Settings\user\applications\etc.....
>
> Then open your file--the links will point to the now correct location.
>
> Now open that file in:
> C:\Documents and Settings\user\applications\etc....
>
> And save it where you want it to really be.
> then close that workbook.
>
> Did it work?
>
>
> SJJoshua wrote:
> >
> > Please help:
> >
> > I have about 330 pivots (yeah, it's a lot) on six different tabs whose
> > source data (range) is located in two different files.
> >
> > My Excel crashed and after I accepted the auto-save updates, the range
> > addresses changed from being located in the same folder (where my
> > pivots are located, [Filename.xls]Sheet1!$A$1:$ZZ$62000) to being the
> > entire address (C:\Documents and Settings\user\applications\etc...).
> >
> > I tried in Edit->Links to 'relink' the correct files; however, when I
> > re-open the file, it continues to reference the entire address.
> >
> > How can I effeciently change all of the range addresses?
> >
> > Thanks,
> >
> > Joshua
>
> --
>
> Dave Peterson
>

Not that I know.

Maybe just changing the source through the wizard would be easiest.

SJJoshua wrote:
>
> Thanks, Dave,
>
> I tried cheating, whereby I did exactly what you suggested. For my data
> that wasn't contained in a pivot, updating the Edit|Links worked fine.
> However, the range addresses for the Pivots remained constant.
>
> Is there any way to look at the code of the Pivots to do a 'Find
> All/Replace' type thing? I certainly hope that what I'm trying to do is
> possible...
>
> Thanks!
>
> "Dave Peterson" wrote:
>
> > I would have guessed that Edit|Links would have worked. You're sure you saved
> > after you did that change?
> >
> > Maybe you can cheat.
> >
> > Move that file that is pointed to to:
> > C:\Documents and Settings\user\applications\etc.....
> >
> > Then open your file--the links will point to the now correct location.
> >
> > Now open that file in:
> > C:\Documents and Settings\user\applications\etc....
> >
> > And save it where you want it to really be.
> > then close that workbook.
> >
> > Did it work?
> >
> >
> > SJJoshua wrote:
> > >
> > > Please help:
> > >
> > > I have about 330 pivots (yeah, it's a lot) on six different tabs whose
> > > source data (range) is located in two different files.
> > >
> > > My Excel crashed and after I accepted the auto-save updates, the range
> > > addresses changed from being located in the same folder (where my
> > > pivots are located, [Filename.xls]Sheet1!$A$1:$ZZ$62000) to being the
> > > entire address (C:\Documents and Settings\user\applications\etc...).
> > >
> > > I tried in Edit->Links to 'relink' the correct files; however, when I
> > > re-open the file, it continues to reference the entire address.
> > >
> > > How can I effeciently change all of the range addresses?
> > >
> > > Thanks,
> > >
> > > Joshua
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson