ACCESS FEED: Re: [MS_AccessPros] Extracting URL from a link

Rabu, 10 April 2013

Re: [MS_AccessPros] Extracting URL from a link

Robin,

Just in case, here's the code to spin through all worksheets. It is the crux of the subroutine, not the whole thing.

Dim I As Long

M = 1

For I = 1 To Sheets.Count
For Each H In Worksheets(I).Hyperlinks
strHyprLink = H.Name
Sheets(2).Select
Cells(M, 1).Value = strHyprLink
strHyprLink = H.Address
Sheets(2).Select
Cells(M, 2).Value = strHyprLink
M = M + 1
Next H
Next I

Jeff

----- Original Message -----

Thanks John,

I'll let you know if I succeed.

Cheers,

Robin

At 10/04/2013 06:32 PM, you wrote:
>Robin-
>
>
>
>I fiddled with importing or linking a spreadsheet that has a Hyperlink, but
>didn't get anywhere. Even when I import the data and specify Hyperlink as
>the data type, it imports only the display text. There might be something
>you could do with Excel automation to open the sheet in Excel and examine
>the underlying URL, but I'm not sure how to do that.
>
>
>
>John Viescas, Author
>
>Microsoft Access 2010 Inside Out
>
>Microsoft Access 2007 Inside Out
>
>Microsoft Access 2003 Inside Out
>
>Building Microsoft Access Applications
>
>SQL Queries for Mere Mortals
>
> http://www.viescas.com/
>
>(Paris, France)
>
>
>
>
>
>
>
>From: MS_Access_Professionals@yahoogroups.com
>[mailto: MS_Access_Professionals@yahoogroups.com ] On Behalf Of Robin Chapple
>Sent: Wednesday, April 10, 2013 8:53 AM
>To: MS_Access_Professionals@yahoogroups.com
>Subject: [MS_AccessPros] Extracting URL from a link
>
>
>
>
>
>I have a spreadsheet with links that are text with an absolute URL like
>this:
>
>< http://abc.com/ >Click here for information
>
>in plain text but "Click here for information" appears and the URL is
>not visible in the spreadsheet.
>
>How do I extract the URL component?
>
>Many thanks,
>
>Robin Chapple
>
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>

--

Jeffrey Park Jones
Excel, Access, Word, Office Expert
Excel and Access, LLC®
http://ExcelAndAccess.Com

919-671-9870
5109 Deer Lake Trail
Wake Forest, NC 27587
jpjones23@centurylink.net

[Non-text portions of this message have been removed]