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

Rabu, 10 April 2013

Re: [MS_AccessPros] Extracting URL from a link

Robin,

A healthy group of time ago I had a need to identify both the name and the associated address of a hyperlink in Excel. The code can easily be modified to identify the address associated with a hyperlink and do what is wanted with it once its in Access. I'm not sire from your description what the requirements are other than to get the address but here's the Excel code I developed.

Sub CopyHyperLink()
'
' CopyHyperLink Macro
' Macro Created 7/16/2004 by Jeffrey P. Jones
'
' This macro will read each hyperlink in a spreadsheet and save off the name and address of each into a cell in a different worksheet.
'
'
Const C_PROC_NAME = "CopyHyperLink"

Dim strHyprLink As String
Dim M As Integer
Dim H As Hyperlink

M = 1

For Each H In Worksheets(1).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
Columns("B:C").Select
Selection.Columns.AutoFit
End Sub ' end CopyHyperLink

I can help further if I have a better understanding of the requirements. This example pulls the hyperlinks from Worksheet 1 but can be changed to spin through all worksheets if needed.

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]