VBA Hyperlinks.Add method prepending local folder to Address

Question

I've been lurking here for a while but this is my first post so let me know if I need to change something. Anyways, here goes:

I'm trying to create a macro that will add hyperlinks to cells in a worksheet. The problem is that after running the macro, I notice that the folder location of my spreadsheet has been prepended to the address that I specified. Is there something I need to do in order to indicate that this is a webpage and not a local file? Excerpt from the macro is below.

Dim IGQ As Range
Dim IGQno As String
Dim IGQno1 As String

For Each IGQ In Range("A2:A10") 'Actual range is much larger

IGQno = IGQ.Value
IGQno1 = Left(IGQ, 1)
Sheets("Cameron DCDA").Hyperlinks.Add Anchor:=IGQ, _
Address:="""http://xxxx""&IGQno1&""xxx""&IGQno&""xxxxx""" 'It's a company website so they probably don't want me to share it

Next

The result is that a hyperlink is created for each cell but it links to file:///C:\Users\John.Doe\Documents\"http://xxxx"&IGQno1&"xxx"&IGQno&"xxxxx"

I've tried using fewer quotation marks in the address since it seems like overkill but I get the compile error "Expected: end of statement"

Do you guys have any suggestions?

IMO it should work with single quotes. What is the content of `IGQno` and `IGQno1` ? Does it maybe contain signs that are not allowed in an URL (like spaces)? — Lukas, Dec 09 '16 at 17:46
An example IGQno would be D30001 so there are no spaces or unusual signs. I tried setting the address as "xxxx"&IGQno1&"xxx"&IGQno&"xxxxx" but I get a compilation error — John Doe, Dec 09 '16 at 17:51
You need spaces around & otherwise it means something else... — Tim Williams, Dec 09 '16 at 17:52

score 1 · Accepted Answer · edited May 23 '17 at 12:01

1

Too many quotes

Address:="http://xxxx " & IGQno1 & "xxx" & IGQno & "xxxxx"

Also - be sure to leave a space before your & otherwise it will be interpreted as a variable type suffix:

What are possible suffixes after variable name in VBA?

edited May 23 '17 at 12:01

Community

1
1

answered Dec 09 '16 at 17:51

Tim Williams

154,628
8
97
125

Thanks, adding the spaces fixed it! I noticed that the & auto-formatted in a previous function that I wrote but I didn't realize it was a requirement – John Doe Dec 09 '16 at 18:06

VBA Hyperlinks.Add method prepending local folder to Address

1 Answers1