Make Tiny URL link of hyperlink in cell

[Ringhal]

Hi all

I have spreadsheet and I am creating a hyperlink that is put together via concatenation from various cells. The result is a very long link that I'd it to be shortened using a service like TinyURL. I know I can manually type in the URL and get a shortened result in the TinyURL service, but I am hoping there is an automated way of doing this. I suspect it has to do with HTML coding, which I have no knowledge, but any help would be greatly appreciated.

[Ringhal]

I will post what I did find and the results.
I found this: http://www.vbaexpress.com/kb/getarticle.php?kb_id=1188,
but couldn't get it to work on multiple cells using a For... Next loop.

Sub EncurtarUmLink()
Dim Token, EndAPI, EndLongo, IniTexto, FimTexto As String
Token = "In_order_to_work_you_need_to_get_a_token_from_Bitly_and_insert_it_here"
Dim HttpReq As New WinHttpRequest
Dim response As String

EndLongo = ActiveCell.Value
EndAPI = "https://api-ssl.bitly.com/v3/shorten?access_token=" & Token & "&longUrl=" & EndLongo


On Error Resume Next 'This is to avoid errors on invalid URLs

With HttpReq
.Open "GET", EndAPI, False
.Send
End With

response = HttpReq.ResponseText
HttpReq.WaitForResponse
IniTexto = InStr(response, "hash")
FimTexto = IniTexto + 15
resultado = Right(Mid(response, IniTexto, (FimTexto - IniTexto)), 7)
ActiveCell.Value = "http://bit.ly/" & resultado
End Sub


And: http://www.jpsoftwaretech.com/shorten-urls-with-bit-ly-web-api-and-vba/,
but the result I got was RATE_LIMIT_EXCEEDED.

Function bitlyURL(txt As String) As String
' based on http://code.google.com/p/bitly-api/wiki/ApiDocumentation
Dim xml As Object
Set xml = CreateObject("MSXML2.XMLHTTP.6.0")

xml.Open "POST", _
"http://api.bit.ly/shorten?version=2.0.1&format=text&login=bitlyapidemo&apiKey=R_0da49e0a9118ff35f52f629d2d71bf07&longUrl=" & _
txt, False
xml.Send

bitlyURL = xml.responsetext
End Function

It could be that I made too many requests, I am not sure of the limit, to the bit.ly server. If this is the case, then I need to make sure that I don't make any new requests for a new link unless the long URL changes. Also, I have about 20 URLs that need to be shortened and the list will grow, but the long URLs will rarely change.

[Kenneth Hobs]

You need to use your own API key. See http://dev.bitly.com/get_started.html

Most sites like that have limits on daily hits and some for total hits.