VBA loop through column and extract embedded hyperlink and create clickable hyperlink

Hello,

I have data in a spreadsheet column 4 that is formatted like this:

"DOC1#http://acmecorp.com/urlfiles/Doc1.pdf"

This an export from MS Access table and the inclusion of the "#" allows the hyperlink to be clickable within the table (MS Accesses way of creating the "friendly name" and "location" in one line.

Additionally, some of the rows have empty column cells and others have just static text without a hyperlink - i.e. "Document unavailable", or "N/A". These just need to be ignore.

I'd like to loop through all rows to reformat the data in each cell so that it's now clickable via the Excel output file.

So the Visible/Friendly name is "DOC1" and the location is the "Http://acmecorp.com/urlfiles/Doc1.pdf" And the "#" is just removed altogether.

VBA needed:

• For loop to check each row in sheet and check data in column 4 cell
• See if there is a hyperlink in the cell "i.e. Find *http://" Then if it's there
• Copy all text AFTER the "#" character
• Store in string
• Delete everything AFTER and INCLUDING the "#" to leave just the name of the doc
• Use remaining text in cell (i.e. DOC1) as the Visible part of the hyperlink
• Paste copied hyperlink from string as the "location" part of the hyperlink
  
• If "http://" is not there, just ignore and move on to next row

Can the HYPERLINK function be used? (HYPERLINK(link_location, [friendly_name]))

Thank you in advance for your assistance.

Update - I've tried the following, but get an error at the ".Hyperlink.Add" part. (Run-time error '483': Object doesn't support this property or method)

Please Login or Register  to view this content.

Maybe this way ?

Please Login or Register  to view this content.