Get Final URLS?

[jimbo222]

Hey everyone,

I was wondering if this is possible and if someone could help......

I want to be able to put urls in an excel file in column A and have a macro that will follow the url and print the final destination url in column B.

For example, I could put a list of shortened urls (i.e. tiny urls) and the macro would output the actual "long" urls in column B.

Thanks for any help!

[Kenneth Hobs]

Welcome to the forum!

Sub Test_GetLongURL()
  MsgBox GetLongURL("http://tinyurl.com/3ju3oly")
End Sub

Function GetLongURL(tinyURL As String) As String
  'This project includes references to "Microsoft Internet Controls", shdocvw.dll and
  '"Microsoft HTML Object Library", mshtml.tlb
'Variable declarations
  Dim myIE As New InternetExplorer 'New '
  Dim myDoc As HTMLDocument
  Dim str As String
'Make IE navigate to the URL and make browser visible
  myIE.navigate tinyURL
  myIE.Visible = False
'Wait for the page to load
  Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
      DoEvents
  Loop
'Set IE document into object
  Set myDoc = myIE.document
  str = myDoc.Location
  Set myDoc = Nothing
  Set myIE = Nothing
  GetLongURL = str
End Function

[jimbo222]

Thank you so much Kenneth! I am really new to this but really appreciate your time. How exactly do I implement this in excel? Also, will this loop through all the urls listed in column A? Thanks!

[Kenneth Hobs]

A UDF is a User Defined Function. Add the code to a Module in the VBE and you will be set. If you type in B1, =GetLongURL(A1), and copy down, it will fill it for you.

If you want to fill column B just once, you can use this method. Note that I started filling from row 2 as row 1 is normally used for column names.

Sub FixShortURLs()
  Dim cell As Range
  For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
    cell.Offset(0, 1).Value2 = GetLongURL(cell.Value2)
  Next cell
End Sub

[jimbo222]

A UDF is a User Defined Function. Add the code to a Module in the VBE and you will be set. If you type in B1, =GetLongURL(A1), and copy down, it will fill it for you.

If you want to fill column B just once, you can use this method. Note that I started filling from row 2 as row 1 is normally used for column names.

Sub FixShortURLs()
  Dim cell As Range
  For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
    cell.Offset(0, 1).Value2 = GetLongURL(cell.Value2)
  Next cell
End Sub

Thanks again. Here's what I'm doing....

1.) I took the code exactly from your first post and pasted into a new module and then saved.
2.) In column A, I have all my urls listed.
3.) In B1 I put =GetLongURL(A1)

When I do this I get a dialog that states: Compile error; User-defined type not defined

In the debugger, the "Function GetLongURL(tinyURL As String) As String" is highlighted.

Any thoughts?

[Kenneth Hobs]

My guess is that you did not set the references as I detailed in the comment. There are two. To add them in the VBE, select the menus Tools > References. This is required for early bound objects. I could have used late binding but early binding lets intellisense work for you.

I would recommend that you add the Compile button to the VBE toolbar or from the menu, Debug > Compile VBAProject. It is usually a good idea to compile a project before you run it.

[jimbo222]

My guess is that you did not set the references as I detailed in the comment. There are two. To add them in the VBE, select the menus Tools > References. This is required for early bound objects. I could have used late binding but early binding lets intellisense work for you.

I would recommend that you add the Compile button to the VBE toolbar or from the menu, Debug > Compile VBAProject. It is usually a good idea to compile a project before you run it.

That was it! Can't thank you enough!