I am attempting to built a database in an Excel spreadsheet. I know that
Access is the better tool, but for various reasons my company's IT section
won't allow us to use Access.

Each row in the spreadsheet has an index number, various other data and
finally a hyperlink which leads to an image of the item. That image can be
anywhere on the hard disk or even the network.

I have things working fine and the hyperlink brings up the image perfectly.

However, I now wish to write a subroutine which will allow all the images to
be copied into a central location, renamed to match the index number with
the hyperlink re-directed to refer to the new copy.

Herein lies the problem. The hyperlinks().address property returns a string
which I intended to use as a fully formed path argument to
filsesystemobject.FileCopy() However, the property behaves differently
depending on where the target file lies.

If the target file lies in the directory tree below the workbook,
hyperlinks().address returns a partial path which can be turned into a fully
formed path by using ThisWorkbook.path & "\" & hyperlinks().address All
well and good.

On the other hand, if the target file is outside the directory tree
containing the workbook, hyperlinks().address returns a string similar to
"..\myfile.jpg"

The tooltip which appears when one places the mouse over the hyperlink
provides exactly what I want, but I cannot find a way to return the tooltip
text.

Is there any way that I can get a rationalised fully formed path from
hyperlinks().address or indeed any other function or property?

Thanks for any help.

--
Enzo

I wear the cheese. It does not wear me.

Wait a minute, I just saw something recently that would take
either a file object or a file path-and-name and return a fully
qualified "absolute" file designation. Let's see here...

Here it is: The FileSystemObject has a method,
GetAbsolutePathName, which "Returns a complete and
unambiguous path from a provided path specification." Its
examples make it clear it's willing to rationalize such
references as "..". Will that help?

--- "Enzo Matrix" wrote:
I am attempting to built a database in an Excel spreadsheet.
Each row in the spreadsheet has...a hyperlink which leads to
an image of the item. That image can be anywhere on the
hard disk or even the network.

I now wish to write a subroutine which will allow all the
images to be copied into a central location, renamed to
match the index number with the hyperlink re-directed to
refer to the new copy. The hyperlinks().address property
returns a string which I intended to use as a fully formed
path argument to filsesystemobject.FileCopy() However,
the property behaves differently depending on where the
target file lies.

If the target file is outside the directory tree containing
the workbook, hyperlinks().address returns a string
similar to "..\myfile.jpg". The tooltip which appears when
one places the mouse over the hyperlink provides exactly
what I want, but I cannot find a way to return the tooltip
text.

Is there any way that I can get a rationalised fully formed path from
hyperlinks().address or indeed any other function or property?

Bob Bridges wrote:
Wait a minute, I just saw something recently that would take
either a file object or a file path-and-name and return a fully
qualified "absolute" file designation. Let's see here...

Here it is: The FileSystemObject has a method,
GetAbsolutePathName, which "Returns a complete and
unambiguous path from a provided path specification." Its
examples make it clear it's willing to rationalize such
references as "..". Will that help?

Thank you!

It didn't work at first, but then I realised that there some anomalies in
the way that the hyperlinks were formatted. Once I sorted them out, it all
worked perfectly.

Thanks again.

--
Enzo

I wear the cheese. It does not wear me.