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.