Excel Hyperlinks Issue

[dblue42]

Hello,

I am importing a lot of survey data from various files into Excel, which gets pasted using the paste special Paste:=xlPasteAll command in VB. One of the columns is full of GPS co-ordinates, formatted as a Google maps link. Most of these are pasted correctly, but occasionally a large set of them get pasted as plain text with no link. I cannot find any reason for this, it appears to be entirely random; there is nothing wrong with the source data (which is a HTML file produced by the survey trolley, opened in Excel and then the various bits are then copied out of it automatically).

I want to run a sub that converts them back into maps links. I have tried the HYPERLINK function, which does what I want but I don't really want the formulas to remain; I just want the hyperlink itself. I cannot get Excel to copy and paste the result of HYPERLINK as HTML, unless I copy it into word first and then copy it back. I've tried various bits of VB code from around the web, but none of them seem to do what I want.

I want a method of replacing the GPS plain text with hyperlinks in the following format:

"https://maps.google.com/?q=" &"{GPS data from column A}" & "&iwloc=A&t=h&z=17"

, use that as the hyperlink and then display the GPS text from column A. (Formatted as code to prevent the forum turning the link into a hyperlink instead of displaying plain text).

I tried to use this code I found here, which I modified slightly with the variable 'TextToDisplay'. It did part of the job, in that it turned the text format google link into a hyperlink, but I couldn't get it to change the displayed text from the full web address to just the GPS address. The code also stops working sometimes, for reasons beyond my skill level (novice).

Sub Convert_Hyperlink()
For Each x In Selection
ActiveSheet.Hyperlinks.Add Anchor:=x, Address:=x.Formula, TextToDisplay:=x.Value
Next x
End Sub

Thanks in advance,

Dan E

GPS sorting.xlsm
	A	B	C
1	GPS	Hyperlink Formula	Full google link
2	53.1380954, -4.2728598	53.1380954, -4.2728598	https://maps.google.com/?q=53.1380954, -4.2728598&iwloc=A&t=h&z=17
3	53.1380954, -4.2728598	53.1380954, -4.2728598	https://maps.google.com/?q=53.1380954, -4.2728598&iwloc=A&t=h&z=17
4	53.1380954, -4.2728598	53.1380954, -4.2728598	https://maps.google.com/?q=53.1380954, -4.2728598&iwloc=A&t=h&z=17
5	53.1380954, -4.2728598	53.1380954, -4.2728598	https://maps.google.com/?q=53.1380954, -4.2728598&iwloc=A&t=h&z=17
6	53.1380954, -4.2728598	53.1380954, -4.2728598	https://maps.google.com/?q=53.1380954, -4.2728598&iwloc=A&t=h&z=17
7	53.1380954, -4.2728598	53.1380954, -4.2728598	https://maps.google.com/?q=53.1380954, -4.2728598&iwloc=A&t=h&z=17
8	53.1380954, -4.2728598	53.1380954, -4.2728598	https://maps.google.com/?q=53.1380954, -4.2728598&iwloc=A&t=h&z=17
9	53.1380954, -4.2728598	53.1380954, -4.2728598	https://maps.google.com/?q=53.1380954, -4.2728598&iwloc=A&t=h&z=17
10	53.1380954, -4.2728598	53.1380954, -4.2728598	https://maps.google.com/?q=53.1380954, -4.2728598&iwloc=A&t=h&z=17
11	53.1380954, -4.2728598	53.1380954, -4.2728598	https://maps.google.com/?q=53.1380954, -4.2728598&iwloc=A&t=h&z=17
12	53.1380871, -4.2728501	53.1380871, -4.2728501	https://maps.google.com/?q=53.1380871, -4.2728501&iwloc=A&t=h&z=17
13	53.1380871, -4.2728501	53.1380871, -4.2728501	https://maps.google.com/?q=53.1380871, -4.2728501&iwloc=A&t=h&z=17
14	53.1380871, -4.2728501	53.1380871, -4.2728501	https://maps.google.com/?q=53.1380871, -4.2728501&iwloc=A&t=h&z=17
15	53.1380871, -4.2728501	53.1380871, -4.2728501	https://maps.google.com/?q=53.1380871, -4.2728501&iwloc=A&t=h&z=17
16	53.1380871, -4.2728501	53.1380871, -4.2728501	https://maps.google.com/?q=53.1380871, -4.2728501&iwloc=A&t=h&z=17
17	53.1380871, -4.2728501	53.1380871, -4.2728501	https://maps.google.com/?q=53.1380871, -4.2728501&iwloc=A&t=h&z=17
18	53.1380871, -4.2728501	53.1380871, -4.2728501	https://maps.google.com/?q=53.1380871, -4.2728501&iwloc=A&t=h&z=17
19	53.1380871, -4.2728501	53.1380871, -4.2728501	https://maps.google.com/?q=53.1380871, -4.2728501&iwloc=A&t=h&z=17
20	53.1380871, -4.2728501	53.1380871, -4.2728501	https://maps.google.com/?q=53.1380871, -4.2728501&iwloc=A&t=h&z=17
21	53.1380786, -4.2728421	53.1380786, -4.2728421	https://maps.google.com/?q=53.1380786, -4.2728421&iwloc=A&t=h&z=17
22	53.1380786, -4.2728421	53.1380786, -4.2728421	https://maps.google.com/?q=53.1380786, -4.2728421&iwloc=A&t=h&z=17
23	53.1380786, -4.2728421	53.1380786, -4.2728421	https://maps.google.com/?q=53.1380786, -4.2728421&iwloc=A&t=h&z=17
24	53.1380786, -4.2728421	53.1380786, -4.2728421	https://maps.google.com/?q=53.1380786, -4.2728421&iwloc=A&t=h&z=17
25	53.1380786, -4.2728421	53.1380786, -4.2728421	https://maps.google.com/?q=53.1380786, -4.2728421&iwloc=A&t=h&z=17
26	53.1380786, -4.2728421	53.1380786, -4.2728421	https://maps.google.com/?q=53.1380786, -4.2728421&iwloc=A&t=h&z=17
27	53.1380786, -4.2728421	53.1380786, -4.2728421	https://maps.google.com/?q=53.1380786, -4.2728421&iwloc=A&t=h&z=17
28	53.1380786, -4.2728421	53.1380786, -4.2728421	https://maps.google.com/?q=53.1380786, -4.2728421&iwloc=A&t=h&z=17
29	53.1380786, -4.2728421	53.1380786, -4.2728421	https://maps.google.com/?q=53.1380786, -4.2728421&iwloc=A&t=h&z=17
30	53.1380786, -4.2728421	53.1380786, -4.2728421	https://maps.google.com/?q=53.1380786, -4.2728421&iwloc=A&t=h&z=17
31	53.1380786, -4.2728421	53.1380786, -4.2728421	https://maps.google.com/?q=53.1380786, -4.2728421&iwloc=A&t=h&z=17
Sheet 1

Cell Formulas
Range	Formula
B2:B31	B2	=HYPERLINK("https://maps.google.com/?q="&A2&"&iwloc=A&t=h&z=17",A2)
C2:C31	C2	=TEXT("https://maps.google.com/?q="&B2&"&iwloc=A&t=h&z=17",B2)

 

[Dan_W]

Hi
I don't have Excel open in front of me to test this, but one thing that jumps out at me is that there is a space between the two coordinates. There shouldn't be a space in URLS, and so I wonder whether this is where the code starts to fall apart. Perhaps try:

Sub Convert_Hyperlink()
    For Each x In Selection
        ActiveSheet.Hyperlinks.Add Anchor:=x, Address:=Replace(x.Value, " ", ""), TextToDisplay:=x.Value
    Next x
End Sub