Looping issue: List of Hyperlinks and Copying from IE to Excel

[shep3369]

Hi,

I'm using the url extension for Google's "I'm feeling lucky" functionality but want to copy the url this function opens (i.e. the direct url). I'm using the below macro which works fine but need it to loop down a list until it finds a blank cell.

I have a list of hyperlinks in column E using the above and want to paste the direct hyperlink in column F.

Any help would be greatly appreciated

Sub GetDirectLink()

Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = False
.Navigate Range("E1")

Do Until .ReadyState = 4: DoEvents: Loop
Application.Wait Now + TimeValue("00:00:01")
Set doc = IE.Document

Cells(1, 5) = IE.Document.URL

IE.Quit

Set IE = Nothing

End With

End Sub

 

[Syoodo]

Are you still looking for an answer?

 

[shep3369]

Are you still looking for an answer?

I managed to create a loop but it is a bit of a slow process and after 20 or so hits to Google, they re-direct to a link that requires a captcha code entered to progress. I'm thinking I need to run this process upon wanting individual links (i.e. when the link is clicked on) rather than trying to run the macro for the entire list at once. I just haven't had the time to play with it further. FYI here is the code:


Sub LoopLink()
' Select first google url
Range("E9").Select
' Run Get Direct Link Sub
'Loop until end
Do Until IsEmpty(ActiveCell)
Call GetDirectLink
Loop
End Sub


Sub GetDirectLink()
'Opens IE Google URL and then copies the direct link into the workbook.
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = False
.Navigate ActiveCell
Do Until .ReadyState = 4: DoEvents: Loop

Set doc = IE.Document

ActiveCell.Offset(0, 1).Select
ActiveCell = IE.Document.URL

ActiveCell.Offset(1, -1).Select

IE.Quit

Set IE = Nothing

End With

End Sub

 

[Syoodo]

I did think that you may be able to use a For ... Next statement to loop through 15 post at a time and then have the macro pause for 5 minutes before resuming. The pause may get around the re-direct to a captcha code page or it may not. The problem is this will slow the process down even more if it works. If it did work you could run it, leave the computer and do something else for however long it will take. At this stage I have no answer that I know will work with the re-direct issue.