If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Save single worksheet as new workbook | GorillaBoze | General Discussion | 6 | July 28th, 2004 08:24 PM |
Exit active workbook & save the changes? | Param | Worksheet Functions | 1 | July 6th, 2004 07:55 PM |
workbook links, remove a bad one | Dave Welsh | General Discussion | 1 | July 2nd, 2004 08:13 PM |
Workbook is taking 2 - 3 minutes to save. Please help | Aaron | General Discussion | 0 | June 23rd, 2004 11:51 PM |
Updating links to another workbook after sorting | Andy at BLE | Worksheet Functions | 0 | June 16th, 2004 09:21 PM |