Losing links when SAVE workbook

Hi All..........

I have a 2200 row database in Excel 2003 that works fine. I have a macro
that will take certain cells in a row and create a hyperlink to an AutoCAD
drawing by that filename. That works fine, or at least it did until just
the other day about row 2134 it started putting the link in just fine like
it always did, but when I save the file, the links go away. The underlined
BLUE text stays, making me think all is ok, but the actual link does not
save............even tho all of the old ones still do. Only those new links
created since 8-31-04, (about row 2134) stop working after saving. It just
keeps happening over and over........I can easily re-create the links, and
they work fine, then SAVE and they all don't work again (Error message
says....."The address of this site is not valid. Check the address and try
again."). Have I reached some sort of a limit, or something? Any ideas
would be appreciated.

Vaya con Dios,
Chuck CABGx3

I haven't encountered this problem myself.
I found a MS KnowledgeBase article which mentioned the error message
http://support.microsoft.com/?id=211571
Might be worth a look to see if it is relevant.

What address does the hyperlink have when it is broken?
Try
?ActiveCell.Hyperlink.Address
in the immediate windoe of the VB editor if all else fails.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Thanks Bill...........

But, things seem to be a little different now than what I first
described......everything I said before is true, the links "go away" when I
SAVE, but they also just "go away" some time after I create them just on
their own, without saving the file..............I'm not sure what you're
asking for the "address on the hyperlink when it is broken".......there are
over 2000 of them on this sheet and they all refer to the same directory on
the same drive on the Network. Now, I also have another sheet in this book
that is a "Batch Processor" of files I select on the first sheet and
transfer over. That second sheet still functions with it's macros and goes
and gets the files and processes them like nothing is wrong.........I just
can't click on the newly created files on the first sheet without getting
the aforementioned Error Message.

This is very serious to me because my whole database is configured around
these links, and if they just "go away" at will, it gives me the shakes as
to what else might happen..........

Here's the macro I use to Concatenate the cells to create the
hyperlink.........

Sub ConcatenateLink()
With ActiveCell
.Value = "\\Surfer\Approved Drawings\" & .Offset(0, -1).Value & "_Rev" &
..Offset(0, 1).Value & _
"_" & .Offset(0, 2).Value & "of" & .Offset(0, 3).Value & ".dwg"
ActiveSheet.Hyperlinks.Add anchor:=ActiveCell, Address:=.Value
End With
End Sub


This one also works if the path/file is already in the cell (IE broken
links)

Sub MakeLinkOfCellContents()
With ActiveCell
ActiveSheet.Hyperlinks.Add anchor:=ActiveCell, Address:=.Value
End With
End Sub

Most of this code is well over my head, I'm mostly just a "recorder", so if
you see anything that's amiss, please help.........

Thanks again,
Vaya con Dios,
Chuck, CABGx3


"Bill Manville" wrote in message
...
I haven't encountered this problem myself.
I found a MS KnowledgeBase article which mentioned the error message
http://support.microsoft.com/?id=211571
Might be worth a look to see if it is relevant.

What address does the hyperlink have when it is broken?
Try
?ActiveCell.Hyperlink.Address
in the immediate windoe of the VB editor if all else fails.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Another development..............several weeks ago, and all before that, I
used to create all the Hyperlinks by hand, by right-click on the cell, then
HYPERLINK, then choosing the file I wanted to link to in the directory, and
actually hand-typing the path to be complete........this was fine when it
only took 15 seconds to access the directory........then all of a sudden
overnight, it started taking 2-3 minutes to access the directory.......heck,
by the time it setteled out, I forgot what I was wanting to do g, so
that's when I started using the macros........anyway, I went back today
after having all this trouble with the Macro-created hyperlinks thinking
maybe this older method was more stable.........WRONG!, they go away just as
fast when SAVE as the Macro-generated ones, so that didn't
help........still, the really older ones are intact.

Our Network access times have been gradually deterioating since various
monitoring means have been employed recently, it even takes almost a minute
to just SAVE my file, which used to be instantaneous.........I feel strongly
like my problem has something to do with the way the network is now handling
Excel files, but I know of no way to test.........oh yeah, they just
introduced a new scan from the server which lists all the software thats on
everybodys computer, for auditing purposes........maybe that has something
to do with it, my computer wouldnt even start this morning until MIS came
and massaged it........

Hoping for more help........

Vaya con Dios,
Chuck, CABGx3




"CLR" wrote in message
...
Thanks Bill...........

But, things seem to be a little different now than what I first
described......everything I said before is true, the links "go away" when
I
SAVE, but they also just "go away" some time after I create them just on
their own, without saving the file..............I'm not sure what you're
asking for the "address on the hyperlink when it is broken".......there
are
over 2000 of them on this sheet and they all refer to the same directory
on
the same drive on the Network. Now, I also have another sheet in this
book
that is a "Batch Processor" of files I select on the first sheet and
transfer over. That second sheet still functions with it's macros and
goes
and gets the files and processes them like nothing is wrong.........I just
can't click on the newly created files on the first sheet without getting
the aforementioned Error Message.

This is very serious to me because my whole database is configured around
these links, and if they just "go away" at will, it gives me the shakes as
to what else might happen..........

Here's the macro I use to Concatenate the cells to create the
hyperlink.........

Sub ConcatenateLink()
With ActiveCell
.Value = "\\Surfer\Approved Drawings\" & .Offset(0, -1).Value & "_Rev"
&
.Offset(0, 1).Value & _
"_" & .Offset(0, 2).Value & "of" & .Offset(0, 3).Value & ".dwg"
ActiveSheet.Hyperlinks.Add anchor:=ActiveCell, Address:=.Value
End With
End Sub


This one also works if the path/file is already in the cell (IE broken
links)

Sub MakeLinkOfCellContents()
With ActiveCell
ActiveSheet.Hyperlinks.Add anchor:=ActiveCell, Address:=.Value
End With
End Sub

Most of this code is well over my head, I'm mostly just a "recorder", so
if
you see anything that's amiss, please help.........

Thanks again,
Vaya con Dios,
Chuck, CABGx3


"Bill Manville" wrote in message
...
I haven't encountered this problem myself.
I found a MS KnowledgeBase article which mentioned the error message
http://support.microsoft.com/?id=211571
Might be worth a look to see if it is relevant.

What address does the hyperlink have when it is broken?
Try
?ActiveCell.Hyperlink.Address
in the immediate windoe of the VB editor if all else fails.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Clr wrote:
Most of this code is well over my head, I'm mostly just a "recorder", so if
you see anything that's amiss, please help.........


I am not a hyperlink expert, and I don't know what's amiss here.

Some problems can be resolved by setting the File / Properties / Hyperlink
Base to C:\

To answer my question about the address of the broken hyperlink, select a
cell containing a broken link and Alt+F11 to the VB editor, Ctrl+G to the
immediate window and
?ActiveCell.Hyperlink.Address
followed by pressing the Enter key.

Let me know what it says.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Ok Bill, thanks and I'll let you know in the morning. It's 9pm here now at
home in St. Petersburg Florida, and I just got back from the Home Depot
store buying some plywood and stuff to put up over my house windows. I'll
go in to work tomorrow, but don't know how long I'll be
there.........hurricane Frances is on her way here.

Hmmmm, I'm beginning to think it's definately a network problem as I just
tried using the file off my little pocket memory stick and the links come up
fine (albiet misdirected cause I'm not hooked to the netowork), but the
little yellow help windows come up also, like they DON'T do at
work...........and I only get an Error message of "Can't open the specified
file" like I use to get at work when it was working but the file was
misplaced......but I only have Excel2k here at home, not 2003 like at
work........we'll see in the morning........I don't really like 2003, and
this may be another good excuse to make them switch me back to
2k................

Thanks again,
Vaya con Dios,
Chuck, CABGx3





"Bill Manville" wrote in message
...
Clr wrote:
Most of this code is well over my head, I'm mostly just a "recorder", so
if
you see anything that's amiss, please help.........


I am not a hyperlink expert, and I don't know what's amiss here.

Some problems can be resolved by setting the File / Properties / Hyperlink
Base to C:\

To answer my question about the address of the broken hyperlink, select a
cell containing a broken link and Alt+F11 to the VB editor, Ctrl+G to the
immediate window and
?ActiveCell.Hyperlink.Address
followed by pressing the Enter key.

Let me know what it says.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Clr wrote:
hurricane Frances is on her way here.


Good luck!

Let me know if the Hyperlink Base idea has any effect.
I have searched the knowledgebase without hitting any other
suggestions.

Are you still able to open it in 2000 on some machine at work and if
so, do recent links work? If you add a link in 2000 does it work when
you open in 2003?


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Hi Bill..........

The bad news is.........

"To answer my question about the address of the broken hyperlink, select a
cell containing a broken link and Alt+F11 to the VB editor, Ctrl+G to the
immediate window and
?ActiveCell.Hyperlink.Address
followed by pressing the Enter key.

Let me know what it says."

I only get an Error message, that says "Object doesn't support this property
or metod".

The good news is..........

Your suggestion about changing the Hyperlink Base to C:\ WORKED!!!!!
All is good now in Excel-land. I appreciate you sticking with me through
this.

For a little more info, the links worked fine when I brought in my file from
home with links installed by XL2k and it worked fine here at work on XL03.
When I brought the file down from the network and put it on my C: drive and
retrieved it from there, it also worked fine, and I made no other changes at
this time. Then I put the file on another directory on the same network
drive, and the problem came back again. Bottom line being, if I get the
file and re-save it to any drive other than the Network, all works fine, but
when i get and save to the Network, the problem pops up again. Then I
"finally" took your suggestion and set the Hyperlink Base to C:\ (it was
just blank before), and VIOLA, everything worked just fine again.......and
incidently, I had to also set it on the other file I had saved in the other
directory, so that action just saves with the file and not Excel.

Thank you, thank you, thank you Bill Manville.........you may not be "a"
Hyperlink Expert (in your words), but you are certainly "MY" Hyperlink
Expert. You really saved my bacon here.

Thanks again, and also thanks for the good wishes about "Frances", we will
all need and appreciate them down here this weekend......I just heard there
are 2.5 million people trying to evacuate, and running out of gas on the
highways.......it's going to be a mess.

Vaya con Dios,
Chuck, CABGx3







"Bill Manville" wrote in message
...
Clr wrote:
hurricane Frances is on her way here.


Good luck!

Let me know if the Hyperlink Base idea has any effect.
I have searched the knowledgebase without hitting any other
suggestions.

Are you still able to open it in 2000 on some machine at work and if
so, do recent links work? If you add a link in 2000 does it work when
you open in 2003?


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Clr wrote:
I only get an Error message, that says "Object doesn't support this property
or metod".


Sorry - my fault - should have said
?ActiveCell.Hyperlinks(1).Address

But academic now since you solved the problem by setting the Hyperlink Base.

Hope Frances keeps away for long enough for you to hide.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Well..........,

But academic now since you solved the problem by setting the Hyperlink
Base.

Maybe yes, maybe no.........everything seemed well and so I put everything
back up on the Network and started using it again and BOOM, now all my "old"
links have changed to my C:\ dirve and of course they don't exist there, so
are effectively dead.....(I do have a macro from the group that will re-set
them for me tho).........and the new ones work, but are going away with a
SAVE again..........

Sorry - my fault - should have said
?ActiveCell.Hyperlinks(1).Address

Tried this and got exactly this (with the two leading dots)
.../Approved Drawings/D28X04302_RevA_2of2.dwg

The full normal link is.......
\\Surfer\Approved Drawings\D28X04302_RevA_2of2.dwg



Trying it on one of the links that are newly displaced, shows only C: and
the filename
C:\D28X04316_RevA_1of2.dwg

The only other thing I can figure to do is to work only off my C: drive and
then copy up to the Network.......funny tho, how this all happened "all of a
sudden" after months and months of working ok using Win2k/Excel2k.

If you have any other ideas or suggestions, I sure would appreciate.........
Thanks again,
Vaya con Dios,
Chuck, CABGx3





"Bill Manville" wrote in message
...
Clr wrote:
I only get an Error message, that says "Object doesn't support this
property
or metod".


Sorry - my fault - should have said
?ActiveCell.Hyperlinks(1).Address

But academic now since you solved the problem by setting the Hyperlink
Base.

Hope Frances keeps away for long enough for you to hide.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup