Robin
Store the URL as a hyperlink data type. The URL would:
Public Function GetURL(varURLandName As Variant) As Variant
Dim strURL As String
'Don't do anything if no string is passed.
If IsNull(varURLandName) Then Exit Function
GetURL = Mid(varURLandName, 2, InStr(varURLandName, ">") - 2)
End Function
The Mid() function takes 3 arguments.: The string, the starting point and the
number of characters you want. In this case the starting point is right after
the < and you want the characters right up to but not including the >. Since you
are starting with the second character and the > is in position 50 the length of
the URL is 48.
Regards,
Bill Mosca,
Founder, MS_Access_Professionals
That'll do IT <http://thatlldoit.com/> http://thatlldoit.com
MS Access MVP
<https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E>
https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
My Nothing-to-do-with Access blog
<http://wrmosca.wordpress.com> http://wrmosca.wordpress.com
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Friday, December 21, 2012 2:58 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Extracting URL from link
I have a spreadsheet which includes hyperlinks. I will be importing
the fields into Access for use on a remote site where the software
will make the 'Page Tile' into a hyperlink by using the URL in
another linked field
I need to extract the actual URL from the hyperlink into a separate field.
For example one existing entry is:
<http://www.rotary9790.org.au/1213/conf/Index.asp>District Conference
I have already extracted the text "District Conference" to a new cell
in another column.
How do I extract the URL?
Many thanks,
Robin Chapple
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar