I have an existing shared excel file in the office server which has hyperlinks to thousands of pdf drawing
files. I'm creating a new file for my own updating purposes and I want to make a column for the hyperlinks to show up only if
I don't have the updated drawings. I've basically completed my file except for the hyperlink part.
target the shared file cell (using "='[FileName]Worksheet'!B2") cause it will only give me back a text without the link.
Q1. Is there a way to copy the hyperlink to my own file? Without having to do it manually. I've tried other thing,
but instead of giving me the correct link (sharedxyzdraw.pdf), it gave me back (flubberxyzdraw.pdf), which is my own computer
I've tried using an UDF, the GetAddress function (to extract the address from a hyperlink), from this
website -> link. It worked, partially. Not sure because of the protected status or the file name is too long (the path name
is indeed very long) or other thing, but the function gave me back "../engineering & drafting/1/2/3/4/5/6/7/8/4089.pdf".
From here own I can just remove the first 2 chars (the 2 '.') and add in the computer name in front of the text
(using "=A1&B1"), where A1 -> "shared" and B1 -> "/engineering & drafting/1/2/3/4/5/6/7/8/4089.pdf". Then I just need to use
the hyperlink function ("hyperlink=(C2,"Click")"), where C2 is the name of the complete path name to the file I want.
But now my problem is that I need to replace "/" in cell B1 to "". And the number of "/" in the path name is
different for each file.
Q2. Is it possible to get a function to replace a specific character 'A' in a text to
another character 'B', irregardless of the number of character 'A' that text has?
A bit too long I know, but any
help will be appreciated. A bunch of thanks.
ps: When I'm using the GetAddress function, I can
only get the address name if the shared file is opened. When the shared file is closed, it will only give me back "#VALUE!".
But if you open the shared file again, it will automatically update and give me the correct address. Any ideas?