PDA

View Full Version : Issues with Hyperlinks



bassnsjp
06-28-2009, 07:24 PM
I'm using MS Office 2003 in a Win XP Pro environment. I don't have access to the PC that has the macro code so, I have to type a small example. There are several issues that came to light just within the last several days, which we were not having any problems before then.

I have a spread sheet that has 100 rows of data and within each row there is 8 columns of data of which three columns contain hyperlinks. Two of the hyperlinks are created manually by inserting a hyperlink by selecting hyperlink from the pulldown menu when you right click on a cell. Then navigate through the directory structure to select the file. These are relative hyperlinks. Both files in these cells are opened and read. The other hyperlink is established using the following command, which creates an absolute hyperlink and it is the output from the macro.

fullpath = "R:\top directory\sub directory\sub directory\"
fileonly = "Inventory.xls"
ActiveCell.Formula = "=HYPERLINK(""" & fullpath & """,''' & fileonly & """)"
Workbooks.Open Filename:=Cells(1, 3) .

Up until a few days ago there were no problems with reading the cells, opening the files and reading the data. Then all of sudden some, not all, links went haywire, here are some of the symptoms:

1) Instead of pointing to the file that the original link was setup with it would point to another file. Sometimes a hyperlink would be a copy of the hyperlink from the cell that was above or below it.

2) Sometimes when the hyperlink was clicked on an error "Cannot open specified file" would be displayed.

3) When running the macro that reads the hyperlinks a run-time error '1004' was received, file could not be found. However, if you clicked on the hyperlink the file would open.

4) Usually when you put the cursor over the cell containing a hyperlink it would display the full pathname and filename. But, sometimes the link would not display and then later on it would. But, if you click on the hyperlink the file would open.

5) The hyperlink would change from an absolute to relative address. For example ..\..\dirname\subdirname\filexyz.xls to C:\dirname\subdirname\filexyz.xls.

6) I checked the Hyperlink Base and it is blank. The files that are being linked to exist in multiple directories thus I cannot establish one common hyperlink base.

7) I wrote a small macro that creates a hyperlink using the formula above and they worked initially and now most do not.

Is there a way to avoid most if not all these symptoms. This is very frustrating especially when everything was working quite well for the last month. The server name or path has not changed. Its odd because some of the hyperlinks both old and new still work with out problems. It appears to be random and I cannot explain it. Any assistances would be greatly appreciated. THANK YOU in ADVANCE.....:banghead:


Steve