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.
Bookmarks