Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Un-Shorten URL

  1. #1
    Board Regular
    Join Date
    Nov 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Un-Shorten URL

    I have about 3,000 shorten URLS. They was converted using Goo.Gl by someone else. Is there a way in Excel that I can unshorten these URLS and populate the full link in a new cell? Cell A2, A3, A4 ect is where I would like to put the new full URL. There is a website (Unshorten any URL - unshort.me) that allows you to unshorten URLS. Itís a pain to go through 3,000 URLS and copying them over to and from the website manually. Is there a way to do this in excel? Macro maybe?

    Here are two URL's for example.
    http://goo.gl/QIp7xJ
    http://goo.gl/Hga4Dw


    Thank you.

  2. #2
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,417
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Un-Shorten URL

    I reckon this'll get you most of them (put it in a standard module):
    Code:
    Public Function unshorten(url As String) As String
        
        Static oRequest As Object
        
        Set oRequest = CreateObject("WinHTTP.WinHTTPRequest.5.1")
        
        With oRequest
            .option(6) = True
            .option(12) = True
            .Open "HEAD", url, False
            .send
            unshorten = .option(1)
        End With
        
    End Function
    It's then used as a worksheet function:
    PHP Code:
    =Unshorten(A1

  3. #3
    Board Regular
    Join Date
    Nov 2013
    Posts
    214
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Un-Shorten URL

    Quote Originally Posted by Kyle123 View Post
    I reckon this'll get you most of them (put it in a standard module):
    Code:
    Public Function unshorten(url As String) As String
        
        Static oRequest As Object
        
        Set oRequest = CreateObject("WinHTTP.WinHTTPRequest.5.1")
        
        With oRequest
            .option(6) = True
            .option(12) = True
            .Open "HEAD", url, False
            .send
            unshorten = .option(1)
        End With
        
    End Function
    It's then used as a worksheet function:
    PHP Code:
    =Unshorten(A1
    That worked perfectly! Thank you very much.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •