PDA

View Full Version : [SOLVED] Get Final URLS?



jimbo222
07-27-2011, 06:32 PM
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
07-28-2011, 01:01 PM
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
07-28-2011, 01:24 PM
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
07-28-2011, 01:32 PM
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
07-28-2011, 06:43 PM
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
07-28-2011, 07:00 PM
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
07-28-2011, 07:18 PM
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!