1

I'm building a VB macro that shortens URLs in bulk (we're talking thousands). My macro works just fine, until I encountered a long URL with "%3d", which translates into the equals symbol (=).

Here is and example of the long URL: http://domain.com/se/?st=YmUQaIg9PCoCs3vex5XHE1NnqfurVpWsXMXix0QkyO4%3d&p=A43S8C

My macro sends the entirety of the URL to Bit.ly, but Bit.ly's response text shows that it shortened this: http://domain.com/se/?st=YmUQaIg9PCoCs3vex5XHE1NnqfurVpWsXMXix0QkyO4=

And in fact, when I go to the shortened link, I'm not directed to the full URL.

This is the portion of my code that prompts Bit.ly and gets the response:

ApiRequest = "https://api-ssl.bitly.com/v3/shorten?access_token=" & Token & "&longUrl=" & LongURL
With HttpRequest
    .Open "GET", ApiRequest, False
    .Send
End With

Response = HttpRequest.ResponseText
HttpRequest.WaitForResponse
BeginCar = InStr(Response, "hash")
EndCar = BeginCar + 15
BitlyResult = Right(Mid(Response, BeginCar, (EndCar - BeginCar)), 7)
Range("J" & l).Value = "http://bit.ly/" & BitlyResult

How can I tell Bit.ly not to shorten a truncated URL and to keep the symbols such as "%3d"?

Thanks,

pnuts
  • 58,317
  • 11
  • 87
  • 139
Peatawn
  • 15
  • 4

1 Answers1

0

I'd think that you must escape the entire url when it is used as a parameter inside another url. The problem may not be %3d but the & that follows after it (it starts the next parameter in the bitly-url).

Try to escape this
http://domain.com/se/?st=YmUQaIg9PCoCs3vex5XHE1NnqfurVpWsXMXix0QkyO4%3d&p=A43S8C
entirely to
http%3A%2F%2Fdomain.com%2Fse%2F%3Fst%3DYmUQaIg9PCoCs3vex5XHE1NnqfurVpWsXMXix0QkyO4%253d%26p%3DA43S8C

Here are ways to do this in Excel.

Community
  • 1
  • 1
KekuSemau
  • 6,830
  • 4
  • 24
  • 34
  • It works! This is great! Thanks! I'll add code to my macro to find/replace all caracters to escape entire URL. Thanks again! – Peatawn Mar 11 '15 at 20:46
  • I deleted my previous comment because I omitted one escape! Sorry. ;-P – Peatawn Mar 11 '15 at 20:47