My links no longer work . . .

mike · 10-25-2005, 07:05 PM

The first thing you will ask is . . did you move the file? The answer is no.
Here's my problem: I have developed fully functional MES and MRP system with
excel. I may have pushed the envelope of what excel can be expected to do as
the MRP workbook itself is about 15 MB and contains several hundred links. I
was very impressed that excel was able to handle this . . . until today (the
system worked quite well for about 6 mos). Now, when I open any of my main
files and update, I only get returns of info on apparently random links. The
majority of them don't work. I am not aware of any changes done that may
account for this -- and why do some links work and some don't (same type of
info from same file - even same column but randomly only a few work) Please,
I need to know of as many possible reasons this may have happened so I can
work on a solution. If I find it, I will post as I see others have had
similar problems.Thanks

Anne Troy · 10-25-2005, 08:05 PM

Somebody did a find and replace in your file?
************
Anne Troy
www.OfficeArticles.com

"mike" <[email protected]> wrote in message
news:[email protected]...
> The first thing you will ask is . . did you move the file? The answer is
> no.
> Here's my problem: I have developed fully functional MES and MRP system
> with
> excel. I may have pushed the envelope of what excel can be expected to do
> as
> the MRP workbook itself is about 15 MB and contains several hundred links.
> I
> was very impressed that excel was able to handle this . . . until today
> (the
> system worked quite well for about 6 mos). Now, when I open any of my main
> files and update, I only get returns of info on apparently random links.
> The
> majority of them don't work. I am not aware of any changes done that may
> account for this -- and why do some links work and some don't (same type
> of
> info from same file - even same column but randomly only a few work)
> Please,
> I need to know of as many possible reasons this may have happened so I can
> work on a solution. If I find it, I will post as I see others have had
> similar problems.Thanks

mike · 10-26-2005, 11:05 AM

If you mean did somebody make changes that may make the links not work, the
answer is no. The links will work if I open all relevant files. If you mean
that using a find and replace in general will cause problems -- This is
possible.
If you read this Anne can you elaborate?

"Anne Troy" wrote:

> Somebody did a find and replace in your file?
> ************
> Anne Troy
> www.OfficeArticles.com
>
> "mike" <[email protected]> wrote in message
> news:[email protected]...
> > The first thing you will ask is . . did you move the file? The answer is
> > no.
> > Here's my problem: I have developed fully functional MES and MRP system
> > with
> > excel. I may have pushed the envelope of what excel can be expected to do
> > as
> > the MRP workbook itself is about 15 MB and contains several hundred links.
> > I
> > was very impressed that excel was able to handle this . . . until today
> > (the
> > system worked quite well for about 6 mos). Now, when I open any of my main
> > files and update, I only get returns of info on apparently random links.
> > The
> > majority of them don't work. I am not aware of any changes done that may
> > account for this -- and why do some links work and some don't (same type
> > of
> > info from same file - even same column but randomly only a few work)
> > Please,
> > I need to know of as many possible reasons this may have happened so I can
> > work on a solution. If I find it, I will post as I see others have had
> > similar problems.Thanks
>
>
>

Anne Troy · 10-26-2005, 12:05 PM

Well, the only thing I could think is that someone did a find and replace,
not realizing it could affect such a thing. I'd have to have specific
examples of what the old link was and the new link--the more examples the
better, of course.
************
Anne Troy
www.OfficeArticles.com

"mike" <[email protected]> wrote in message
news:[email protected]...
> If you mean did somebody make changes that may make the links not work,
> the
> answer is no. The links will work if I open all relevant files. If you
> mean
> that using a find and replace in general will cause problems -- This is
> possible.
> If you read this Anne can you elaborate?
>
> "Anne Troy" wrote:
>
>> Somebody did a find and replace in your file?
>> ************
>> Anne Troy
>> www.OfficeArticles.com
>>
>> "mike" <[email protected]> wrote in message
>> news:[email protected]...
>> > The first thing you will ask is . . did you move the file? The answer
>> > is
>> > no.
>> > Here's my problem: I have developed fully functional MES and MRP system
>> > with
>> > excel. I may have pushed the envelope of what excel can be expected to
>> > do
>> > as
>> > the MRP workbook itself is about 15 MB and contains several hundred
>> > links.
>> > I
>> > was very impressed that excel was able to handle this . . . until today
>> > (the
>> > system worked quite well for about 6 mos). Now, when I open any of my
>> > main
>> > files and update, I only get returns of info on apparently random
>> > links.
>> > The
>> > majority of them don't work. I am not aware of any changes done that
>> > may
>> > account for this -- and why do some links work and some don't (same
>> > type
>> > of
>> > info from same file - even same column but randomly only a few work)
>> > Please,
>> > I need to know of as many possible reasons this may have happened so I
>> > can
>> > work on a solution. If I find it, I will post as I see others have had
>> > similar problems.Thanks
>>
>>
>>

mike · 10-26-2005, 04:05 PM

Ok, I guess I should give you more info -- This will probably baffle you.
All the files are located on the server. There are about 4 users who
regularly access the files but I'm assuming I'm the only one who makes
changes to critical info (not all files are password protected). The links
are identical to the pre-problem links (first thing i checked).
For example: I have a main inventory file with item codes and item
descriptions (and a whole bunch of other stuff but its not relevant). My MRP
file has item codes and vlookups to the item descriptions (descriptions
change so i only wanted one source for the descriptions). When I open the MRP
and click update it will retrieve some of the descriptions but not all (less
than 50%)--(actually that was yesterday, today I get an "unable to open file"
error when it tries to link and nothing gets updated).
However, I can easily go to the location indicated in the link, open the
file that it needs to link to and everything works peachy (except my computer
lags from having all the large files open simultaneously). The odd thing is
also that this is affecting more than one file--someone would have had to do
the search and replace on multiple files (although possible) that are not
even related.
My final thought is: Can this be something other than Excel? (server
problem, network problem, windows problem etc?)

"Anne Troy" wrote:

> Well, the only thing I could think is that someone did a find and replace,
> not realizing it could affect such a thing. I'd have to have specific
> examples of what the old link was and the new link--the more examples the
> better, of course.
> ************
> Anne Troy
> www.OfficeArticles.com
>
> "mike" <[email protected]> wrote in message
> news:[email protected]...
> > If you mean did somebody make changes that may make the links not work,
> > the
> > answer is no. The links will work if I open all relevant files. If you
> > mean
> > that using a find and replace in general will cause problems -- This is
> > possible.
> > If you read this Anne can you elaborate?
> >
> > "Anne Troy" wrote:
> >
> >> Somebody did a find and replace in your file?
> >> ************
> >> Anne Troy
> >> www.OfficeArticles.com
> >>
> >> "mike" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > The first thing you will ask is . . did you move the file? The answer
> >> > is
> >> > no.
> >> > Here's my problem: I have developed fully functional MES and MRP system
> >> > with
> >> > excel. I may have pushed the envelope of what excel can be expected to
> >> > do
> >> > as
> >> > the MRP workbook itself is about 15 MB and contains several hundred
> >> > links.
> >> > I
> >> > was very impressed that excel was able to handle this . . . until today
> >> > (the
> >> > system worked quite well for about 6 mos). Now, when I open any of my
> >> > main
> >> > files and update, I only get returns of info on apparently random
> >> > links.
> >> > The
> >> > majority of them don't work. I am not aware of any changes done that
> >> > may
> >> > account for this -- and why do some links work and some don't (same
> >> > type
> >> > of
> >> > info from same file - even same column but randomly only a few work)
> >> > Please,
> >> > I need to know of as many possible reasons this may have happened so I
> >> > can
> >> > work on a solution. If I find it, I will post as I see others have had
> >> > similar problems.Thanks
> >>
> >>
> >>
>
>
>

Anne Troy · 10-26-2005, 05:05 PM

Quite honestly, Mike, when I hear people opening "so many workbooks" and
having "so many links", I begin to think you'd be better off using Access.
It's just designed to handle relationships like this better than Excel, tho
I've never claimed to be any good at relationships, LOL. Anyway, to get you
beyond your current dilemma... it sounds to me like SOME of the links could
be updating and, due to lag or resources on your PC, others are not updating
or are just getting lost somehow. I can only tell you what I would do if I
were you and converting to a *real* DB isn't an option:
1. Clean up my hard drive:
http://www.officearticles.com/misc/h...hard_drive.htm so
you know you've got all the resources you possibly can (I realize this might
apply to other PCs too).
2. Clean up the workbooks/worksheets as good as you possibly can:
http://www.officearticles.com/excel/...soft_excel.htm
http://www.officearticles.com/excel/...soft_excel.htm
I only include the 2nd article in case any of these workbooks seem bloated.
3. Create a macro (you can record this one, I think) that opens all the
other files, or at least those necessary for efficiency when working with
your workbook, when you open your main file. Put that macro in the Workbook
open by right-clicking any sheet tab and hit View Code. Then on the left,
double-click This Workbook. Then from the dropdowns on the right, choose
Workbook. Then paste your recorded code over on the right. It should end up
looking something like this:
_______________________________
Private Sub Workbook_Open()
Workbooks.Open Filename:= _
"C:\Documents and
Settings\Administrator\Desktop\RestaurantRetrieverFastFood.xls"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Book1.xls"
Workbooks.Open Filename:= _
"C:\Documents and
Settings\Administrator\Desktop\RestaurantRetriever.xls"
End Sub
__________________________________
Save and close your file, then reopen to test. (I'm sure the code could be
more efficient, but it's not lengthy code anyway).

Hopefully, others will also have suggestions.
************
Anne Troy
www.OfficeArticles.com

"mike" <[email protected]> wrote in message
news:[email protected]...
> Ok, I guess I should give you more info -- This will probably baffle you.
> All the files are located on the server. There are about 4 users who
> regularly access the files but I'm assuming I'm the only one who makes
> changes to critical info (not all files are password protected). The links
> are identical to the pre-problem links (first thing i checked).
> For example: I have a main inventory file with item codes and item
> descriptions (and a whole bunch of other stuff but its not relevant). My
> MRP
> file has item codes and vlookups to the item descriptions (descriptions
> change so i only wanted one source for the descriptions). When I open the
> MRP
> and click update it will retrieve some of the descriptions but not all
> (less
> than 50%)--(actually that was yesterday, today I get an "unable to open
> file"
> error when it tries to link and nothing gets updated).
> However, I can easily go to the location indicated in the link, open the
> file that it needs to link to and everything works peachy (except my
> computer
> lags from having all the large files open simultaneously). The odd thing
> is
> also that this is affecting more than one file--someone would have had to
> do
> the search and replace on multiple files (although possible) that are not
> even related.
> My final thought is: Can this be something other than Excel? (server
> problem, network problem, windows problem etc?)
>
>
> "Anne Troy" wrote:
>
>> Well, the only thing I could think is that someone did a find and
>> replace,
>> not realizing it could affect such a thing. I'd have to have specific
>> examples of what the old link was and the new link--the more examples the
>> better, of course.
>> ************
>> Anne Troy
>> www.OfficeArticles.com
>>
>> "mike" <[email protected]> wrote in message
>> news:[email protected]...
>> > If you mean did somebody make changes that may make the links not work,
>> > the
>> > answer is no. The links will work if I open all relevant files. If you
>> > mean
>> > that using a find and replace in general will cause problems -- This is
>> > possible.
>> > If you read this Anne can you elaborate?
>> >
>> > "Anne Troy" wrote:
>> >
>> >> Somebody did a find and replace in your file?
>> >> ************
>> >> Anne Troy
>> >> www.OfficeArticles.com
>> >>
>> >> "mike" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > The first thing you will ask is . . did you move the file? The
>> >> > answer
>> >> > is
>> >> > no.
>> >> > Here's my problem: I have developed fully functional MES and MRP
>> >> > system
>> >> > with
>> >> > excel. I may have pushed the envelope of what excel can be expected
>> >> > to
>> >> > do
>> >> > as
>> >> > the MRP workbook itself is about 15 MB and contains several hundred
>> >> > links.
>> >> > I
>> >> > was very impressed that excel was able to handle this . . . until
>> >> > today
>> >> > (the
>> >> > system worked quite well for about 6 mos). Now, when I open any of
>> >> > my
>> >> > main
>> >> > files and update, I only get returns of info on apparently random
>> >> > links.
>> >> > The
>> >> > majority of them don't work. I am not aware of any changes done that
>> >> > may
>> >> > account for this -- and why do some links work and some don't (same
>> >> > type
>> >> > of
>> >> > info from same file - even same column but randomly only a few work)
>> >> > Please,
>> >> > I need to know of as many possible reasons this may have happened so
>> >> > I
>> >> > can
>> >> > work on a solution. If I find it, I will post as I see others have
>> >> > had
>> >> > similar problems.Thanks
>> >>
>> >>
>> >>
>>
>>
>>

mike · 10-26-2005, 06:05 PM

Thanks Anne,
I'll keep workinging on this, I think I may have it figured out. The
Workstation that does all the writing to the inventory file was recently
upgraded. The tech guy may have used a different route to the file when he
created the new shortcuts (through network places rather than just
T:\blah\blah) or something to do with that. Theoretically it should still
work but I have had something similar happen were one route allows access to
the file and the other does not. I agree that Access would have been alot
better but I needed a system fast and I don't know much about it

. I only
need this system to work for 6 more months then I get to buy an ERP system
(yay!)

Thanks for the macro suggestion--if I can't fix it that should get us
through for the next while.

I'll post a solution--I'm getting closer . . . i can feel it . . . ; )

"Anne Troy" wrote:

> Quite honestly, Mike, when I hear people opening "so many workbooks" and
> having "so many links", I begin to think you'd be better off using Access.
> It's just designed to handle relationships like this better than Excel, tho
> I've never claimed to be any good at relationships, LOL. Anyway, to get you
> beyond your current dilemma... it sounds to me like SOME of the links could
> be updating and, due to lag or resources on your PC, others are not updating
> or are just getting lost somehow. I can only tell you what I would do if I
> were you and converting to a *real* DB isn't an option:
> 1. Clean up my hard drive:
> http://www.officearticles.com/misc/h...hard_drive.htm so
> you know you've got all the resources you possibly can (I realize this might
> apply to other PCs too).
> 2. Clean up the workbooks/worksheets as good as you possibly can:
> http://www.officearticles.com/excel/...soft_excel.htm
> http://www.officearticles.com/excel/...soft_excel.htm
> I only include the 2nd article in case any of these workbooks seem bloated.
> 3. Create a macro (you can record this one, I think) that opens all the
> other files, or at least those necessary for efficiency when working with
> your workbook, when you open your main file. Put that macro in the Workbook
> open by right-clicking any sheet tab and hit View Code. Then on the left,
> double-click This Workbook. Then from the dropdowns on the right, choose
> Workbook. Then paste your recorded code over on the right. It should end up
> looking something like this:
> _______________________________
> Private Sub Workbook_Open()
> Workbooks.Open Filename:= _
> "C:\Documents and
> Settings\Administrator\Desktop\RestaurantRetrieverFastFood.xls"
> Workbooks.Open Filename:= _
> "C:\Documents and Settings\Administrator\Desktop\Book1.xls"
> Workbooks.Open Filename:= _
> "C:\Documents and
> Settings\Administrator\Desktop\RestaurantRetriever.xls"
> End Sub
> __________________________________
> Save and close your file, then reopen to test. (I'm sure the code could be
> more efficient, but it's not lengthy code anyway).
>
> Hopefully, others will also have suggestions.
> ************
> Anne Troy
> www.OfficeArticles.com
>
> "mike" <[email protected]> wrote in message
> news:[email protected]...
> > Ok, I guess I should give you more info -- This will probably baffle you.
> > All the files are located on the server. There are about 4 users who
> > regularly access the files but I'm assuming I'm the only one who makes
> > changes to critical info (not all files are password protected). The links
> > are identical to the pre-problem links (first thing i checked).
> > For example: I have a main inventory file with item codes and item
> > descriptions (and a whole bunch of other stuff but its not relevant). My
> > MRP
> > file has item codes and vlookups to the item descriptions (descriptions
> > change so i only wanted one source for the descriptions). When I open the
> > MRP
> > and click update it will retrieve some of the descriptions but not all
> > (less
> > than 50%)--(actually that was yesterday, today I get an "unable to open
> > file"
> > error when it tries to link and nothing gets updated).
> > However, I can easily go to the location indicated in the link, open the
> > file that it needs to link to and everything works peachy (except my
> > computer
> > lags from having all the large files open simultaneously). The odd thing
> > is
> > also that this is affecting more than one file--someone would have had to
> > do
> > the search and replace on multiple files (although possible) that are not
> > even related.
> > My final thought is: Can this be something other than Excel? (server
> > problem, network problem, windows problem etc?)
> >
> >
> > "Anne Troy" wrote:
> >
> >> Well, the only thing I could think is that someone did a find and
> >> replace,
> >> not realizing it could affect such a thing. I'd have to have specific
> >> examples of what the old link was and the new link--the more examples the
> >> better, of course.
> >> ************
> >> Anne Troy
> >> www.OfficeArticles.com
> >>
> >> "mike" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > If you mean did somebody make changes that may make the links not work,
> >> > the
> >> > answer is no. The links will work if I open all relevant files. If you
> >> > mean
> >> > that using a find and replace in general will cause problems -- This is
> >> > possible.
> >> > If you read this Anne can you elaborate?
> >> >
> >> > "Anne Troy" wrote:
> >> >
> >> >> Somebody did a find and replace in your file?
> >> >> ************
> >> >> Anne Troy
> >> >> www.OfficeArticles.com
> >> >>
> >> >> "mike" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > The first thing you will ask is . . did you move the file? The
> >> >> > answer
> >> >> > is
> >> >> > no.
> >> >> > Here's my problem: I have developed fully functional MES and MRP
> >> >> > system
> >> >> > with
> >> >> > excel. I may have pushed the envelope of what excel can be expected
> >> >> > to
> >> >> > do
> >> >> > as
> >> >> > the MRP workbook itself is about 15 MB and contains several hundred
> >> >> > links.
> >> >> > I
> >> >> > was very impressed that excel was able to handle this . . . until
> >> >> > today
> >> >> > (the
> >> >> > system worked quite well for about 6 mos). Now, when I open any of
> >> >> > my
> >> >> > main
> >> >> > files and update, I only get returns of info on apparently random
> >> >> > links.
> >> >> > The
> >> >> > majority of them don't work. I am not aware of any changes done that
> >> >> > may
> >> >> > account for this -- and why do some links work and some don't (same
> >> >> > type
> >> >> > of
> >> >> > info from same file - even same column but randomly only a few work)
> >> >> > Please,
> >> >> > I need to know of as many possible reasons this may have happened so
> >> >> > I
> >> >> > can
> >> >> > work on a solution. If I find it, I will post as I see others have
> >> >> > had
> >> >> > similar problems.Thanks
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

mike · 10-27-2005, 06:05 PM

SOLUTION FOUND!

As is often with these sort of things the problem was caused by a small
thing that was easy to fix -- trying to find it was the hard part.

Any one else with this type of problem check these things:

did a file get moved?
are the links pointing to the correct address?
Make sure the workbook being linked to is not protected.

mMy problem was the last one. someone accidently protected workbook after
making a change rather than protecting sheet.

I hope this helps someone

"mike" wrote:

> Thanks Anne,
> I'll keep workinging on this, I think I may have it figured out. The
> Workstation that does all the writing to the inventory file was recently
> upgraded. The tech guy may have used a different route to the file when he
> created the new shortcuts (through network places rather than just
> T:\blah\blah) or something to do with that. Theoretically it should still
> work but I have had something similar happen were one route allows access to
> the file and the other does not. I agree that Access would have been alot
> better but I needed a system fast and I don't know much about it

. I only
> need this system to work for 6 more months then I get to buy an ERP system
> (yay!)
>
> Thanks for the macro suggestion--if I can't fix it that should get us
> through for the next while.
>
> I'll post a solution--I'm getting closer . . . i can feel it . . . ; )
>
>
>
> "Anne Troy" wrote:
>
> > Quite honestly, Mike, when I hear people opening "so many workbooks" and
> > having "so many links", I begin to think you'd be better off using Access.
> > It's just designed to handle relationships like this better than Excel, tho
> > I've never claimed to be any good at relationships, LOL. Anyway, to get you
> > beyond your current dilemma... it sounds to me like SOME of the links could
> > be updating and, due to lag or resources on your PC, others are not updating
> > or are just getting lost somehow. I can only tell you what I would do if I
> > were you and converting to a *real* DB isn't an option:
> > 1. Clean up my hard drive:
> > http://www.officearticles.com/misc/h...hard_drive.htm so
> > you know you've got all the resources you possibly can (I realize this might
> > apply to other PCs too).
> > 2. Clean up the workbooks/worksheets as good as you possibly can:
> > http://www.officearticles.com/excel/...soft_excel.htm
> > http://www.officearticles.com/excel/...soft_excel.htm
> > I only include the 2nd article in case any of these workbooks seem bloated.
> > 3. Create a macro (you can record this one, I think) that opens all the
> > other files, or at least those necessary for efficiency when working with
> > your workbook, when you open your main file. Put that macro in the Workbook
> > open by right-clicking any sheet tab and hit View Code. Then on the left,
> > double-click This Workbook. Then from the dropdowns on the right, choose
> > Workbook. Then paste your recorded code over on the right. It should end up
> > looking something like this:
> > _______________________________
> > Private Sub Workbook_Open()
> > Workbooks.Open Filename:= _
> > "C:\Documents and
> > Settings\Administrator\Desktop\RestaurantRetrieverFastFood.xls"
> > Workbooks.Open Filename:= _
> > "C:\Documents and Settings\Administrator\Desktop\Book1.xls"
> > Workbooks.Open Filename:= _
> > "C:\Documents and
> > Settings\Administrator\Desktop\RestaurantRetriever.xls"
> > End Sub
> > __________________________________
> > Save and close your file, then reopen to test. (I'm sure the code could be
> > more efficient, but it's not lengthy code anyway).
> >
> > Hopefully, others will also have suggestions.
> > ************
> > Anne Troy
> > www.OfficeArticles.com
> >
> > "mike" <[email protected]> wrote in message
> > news:[email protected]...
> > > Ok, I guess I should give you more info -- This will probably baffle you.
> > > All the files are located on the server. There are about 4 users who
> > > regularly access the files but I'm assuming I'm the only one who makes
> > > changes to critical info (not all files are password protected). The links
> > > are identical to the pre-problem links (first thing i checked).
> > > For example: I have a main inventory file with item codes and item
> > > descriptions (and a whole bunch of other stuff but its not relevant). My
> > > MRP
> > > file has item codes and vlookups to the item descriptions (descriptions
> > > change so i only wanted one source for the descriptions). When I open the
> > > MRP
> > > and click update it will retrieve some of the descriptions but not all
> > > (less
> > > than 50%)--(actually that was yesterday, today I get an "unable to open
> > > file"
> > > error when it tries to link and nothing gets updated).
> > > However, I can easily go to the location indicated in the link, open the
> > > file that it needs to link to and everything works peachy (except my
> > > computer
> > > lags from having all the large files open simultaneously). The odd thing
> > > is
> > > also that this is affecting more than one file--someone would have had to
> > > do
> > > the search and replace on multiple files (although possible) that are not
> > > even related.
> > > My final thought is: Can this be something other than Excel? (server
> > > problem, network problem, windows problem etc?)
> > >
> > >
> > > "Anne Troy" wrote:
> > >
> > >> Well, the only thing I could think is that someone did a find and
> > >> replace,
> > >> not realizing it could affect such a thing. I'd have to have specific
> > >> examples of what the old link was and the new link--the more examples the
> > >> better, of course.
> > >> ************
> > >> Anne Troy
> > >> www.OfficeArticles.com
> > >>
> > >> "mike" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > If you mean did somebody make changes that may make the links not work,
> > >> > the
> > >> > answer is no. The links will work if I open all relevant files. If you
> > >> > mean
> > >> > that using a find and replace in general will cause problems -- This is
> > >> > possible.
> > >> > If you read this Anne can you elaborate?
> > >> >
> > >> > "Anne Troy" wrote:
> > >> >
> > >> >> Somebody did a find and replace in your file?
> > >> >> ************
> > >> >> Anne Troy
> > >> >> www.OfficeArticles.com
> > >> >>
> > >> >> "mike" <[email protected]> wrote in message
> > >> >> news:[email protected]...
> > >> >> > The first thing you will ask is . . did you move the file? The
> > >> >> > answer
> > >> >> > is
> > >> >> > no.
> > >> >> > Here's my problem: I have developed fully functional MES and MRP
> > >> >> > system
> > >> >> > with
> > >> >> > excel. I may have pushed the envelope of what excel can be expected
> > >> >> > to
> > >> >> > do
> > >> >> > as
> > >> >> > the MRP workbook itself is about 15 MB and contains several hundred
> > >> >> > links.
> > >> >> > I
> > >> >> > was very impressed that excel was able to handle this . . . until
> > >> >> > today
> > >> >> > (the
> > >> >> > system worked quite well for about 6 mos). Now, when I open any of
> > >> >> > my
> > >> >> > main
> > >> >> > files and update, I only get returns of info on apparently random
> > >> >> > links.
> > >> >> > The
> > >> >> > majority of them don't work. I am not aware of any changes done that
> > >> >> > may
> > >> >> > account for this -- and why do some links work and some don't (same
> > >> >> > type
> > >> >> > of
> > >> >> > info from same file - even same column but randomly only a few work)
> > >> >> > Please,
> > >> >> > I need to know of as many possible reasons this may have happened so
> > >> >> > I
> > >> >> > can
> > >> >> > work on a solution. If I find it, I will post as I see others have
> > >> >> > had
> > >> >> > similar problems.Thanks
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

Anne Troy · 10-27-2005, 07:05 PM

LOL, Mike.

************
Anne Troy
www.OfficeArticles.com

"mike" <[email protected]> wrote in message
news:[email protected]...
> SOLUTION FOUND!
>
> As is often with these sort of things the problem was caused by a small
> thing that was easy to fix -- trying to find it was the hard part.
>
> Any one else with this type of problem check these things:
>
> did a file get moved?
> are the links pointing to the correct address?
> Make sure the workbook being linked to is not protected.
>
> mMy problem was the last one. someone accidently protected workbook after
> making a change rather than protecting sheet.
>
> I hope this helps someone

>
>
>
> "mike" wrote:
>
>> Thanks Anne,
>> I'll keep workinging on this, I think I may have it figured out. The
>> Workstation that does all the writing to the inventory file was recently
>> upgraded. The tech guy may have used a different route to the file when
>> he
>> created the new shortcuts (through network places rather than just
>> T:\blah\blah) or something to do with that. Theoretically it should still
>> work but I have had something similar happen were one route allows access
>> to
>> the file and the other does not. I agree that Access would have been alot
>> better but I needed a system fast and I don't know much about it

. I
>> only
>> need this system to work for 6 more months then I get to buy an ERP
>> system
>> (yay!)
>>
>> Thanks for the macro suggestion--if I can't fix it that should get us
>> through for the next while.
>>
>> I'll post a solution--I'm getting closer . . . i can feel it . . . ; )
>>
>>
>>
>> "Anne Troy" wrote:
>>
>> > Quite honestly, Mike, when I hear people opening "so many workbooks"
>> > and
>> > having "so many links", I begin to think you'd be better off using
>> > Access.
>> > It's just designed to handle relationships like this better than Excel,
>> > tho
>> > I've never claimed to be any good at relationships, LOL. Anyway, to get
>> > you
>> > beyond your current dilemma... it sounds to me like SOME of the links
>> > could
>> > be updating and, due to lag or resources on your PC, others are not
>> > updating
>> > or are just getting lost somehow. I can only tell you what I would do
>> > if I
>> > were you and converting to a *real* DB isn't an option:
>> > 1. Clean up my hard drive:
>> > http://www.officearticles.com/misc/h...hard_drive.htm
>> > so
>> > you know you've got all the resources you possibly can (I realize this
>> > might
>> > apply to other PCs too).
>> > 2. Clean up the workbooks/worksheets as good as you possibly can:
>> > http://www.officearticles.com/excel/...soft_excel.htm
>> > http://www.officearticles.com/excel/...soft_excel.htm
>> > I only include the 2nd article in case any of these workbooks seem
>> > bloated.
>> > 3. Create a macro (you can record this one, I think) that opens all the
>> > other files, or at least those necessary for efficiency when working
>> > with
>> > your workbook, when you open your main file. Put that macro in the
>> > Workbook
>> > open by right-clicking any sheet tab and hit View Code. Then on the
>> > left,
>> > double-click This Workbook. Then from the dropdowns on the right,
>> > choose
>> > Workbook. Then paste your recorded code over on the right. It should
>> > end up
>> > looking something like this:
>> > _______________________________
>> > Private Sub Workbook_Open()
>> > Workbooks.Open Filename:= _
>> > "C:\Documents and
>> > Settings\Administrator\Desktop\RestaurantRetrieverFastFood.xls"
>> > Workbooks.Open Filename:= _
>> > "C:\Documents and Settings\Administrator\Desktop\Book1.xls"
>> > Workbooks.Open Filename:= _
>> > "C:\Documents and
>> > Settings\Administrator\Desktop\RestaurantRetriever.xls"
>> > End Sub
>> > __________________________________
>> > Save and close your file, then reopen to test. (I'm sure the code could
>> > be
>> > more efficient, but it's not lengthy code anyway).
>> >
>> > Hopefully, others will also have suggestions.
>> > ************
>> > Anne Troy
>> > www.OfficeArticles.com
>> >
>> > "mike" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > Ok, I guess I should give you more info -- This will probably baffle
>> > > you.
>> > > All the files are located on the server. There are about 4 users who
>> > > regularly access the files but I'm assuming I'm the only one who
>> > > makes
>> > > changes to critical info (not all files are password protected). The
>> > > links
>> > > are identical to the pre-problem links (first thing i checked).
>> > > For example: I have a main inventory file with item codes and item
>> > > descriptions (and a whole bunch of other stuff but its not relevant).
>> > > My
>> > > MRP
>> > > file has item codes and vlookups to the item descriptions
>> > > (descriptions
>> > > change so i only wanted one source for the descriptions). When I open
>> > > the
>> > > MRP
>> > > and click update it will retrieve some of the descriptions but not
>> > > all
>> > > (less
>> > > than 50%)--(actually that was yesterday, today I get an "unable to
>> > > open
>> > > file"
>> > > error when it tries to link and nothing gets updated).
>> > > However, I can easily go to the location indicated in the link, open
>> > > the
>> > > file that it needs to link to and everything works peachy (except my
>> > > computer
>> > > lags from having all the large files open simultaneously). The odd
>> > > thing
>> > > is
>> > > also that this is affecting more than one file--someone would have
>> > > had to
>> > > do
>> > > the search and replace on multiple files (although possible) that are
>> > > not
>> > > even related.
>> > > My final thought is: Can this be something other than Excel? (server
>> > > problem, network problem, windows problem etc?)
>> > >
>> > >
>> > > "Anne Troy" wrote:
>> > >
>> > >> Well, the only thing I could think is that someone did a find and
>> > >> replace,
>> > >> not realizing it could affect such a thing. I'd have to have
>> > >> specific
>> > >> examples of what the old link was and the new link--the more
>> > >> examples the
>> > >> better, of course.
>> > >> ************
>> > >> Anne Troy
>> > >> www.OfficeArticles.com
>> > >>
>> > >> "mike" <[email protected]> wrote in message
>> > >> news:[email protected]...
>> > >> > If you mean did somebody make changes that may make the links not
>> > >> > work,
>> > >> > the
>> > >> > answer is no. The links will work if I open all relevant files. If
>> > >> > you
>> > >> > mean
>> > >> > that using a find and replace in general will cause problems --
>> > >> > This is
>> > >> > possible.
>> > >> > If you read this Anne can you elaborate?
>> > >> >
>> > >> > "Anne Troy" wrote:
>> > >> >
>> > >> >> Somebody did a find and replace in your file?
>> > >> >> ************
>> > >> >> Anne Troy
>> > >> >> www.OfficeArticles.com
>> > >> >>
>> > >> >> "mike" <[email protected]> wrote in message
>> > >> >> news:[email protected]...
>> > >> >> > The first thing you will ask is . . did you move the file? The
>> > >> >> > answer
>> > >> >> > is
>> > >> >> > no.
>> > >> >> > Here's my problem: I have developed fully functional MES and
>> > >> >> > MRP
>> > >> >> > system
>> > >> >> > with
>> > >> >> > excel. I may have pushed the envelope of what excel can be
>> > >> >> > expected
>> > >> >> > to
>> > >> >> > do
>> > >> >> > as
>> > >> >> > the MRP workbook itself is about 15 MB and contains several
>> > >> >> > hundred
>> > >> >> > links.
>> > >> >> > I
>> > >> >> > was very impressed that excel was able to handle this . . .
>> > >> >> > until
>> > >> >> > today
>> > >> >> > (the
>> > >> >> > system worked quite well for about 6 mos). Now, when I open any
>> > >> >> > of
>> > >> >> > my
>> > >> >> > main
>> > >> >> > files and update, I only get returns of info on apparently
>> > >> >> > random
>> > >> >> > links.
>> > >> >> > The
>> > >> >> > majority of them don't work. I am not aware of any changes done
>> > >> >> > that
>> > >> >> > may
>> > >> >> > account for this -- and why do some links work and some don't
>> > >> >> > (same
>> > >> >> > type
>> > >> >> > of
>> > >> >> > info from same file - even same column but randomly only a few
>> > >> >> > work)
>> > >> >> > Please,
>> > >> >> > I need to know of as many possible reasons this may have
>> > >> >> > happened so
>> > >> >> > I
>> > >> >> > can
>> > >> >> > work on a solution. If I find it, I will post as I see others
>> > >> >> > have
>> > >> >> > had
>> > >> >> > similar problems.Thanks
>> > >> >>
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >