loop of urls

eran3185

Board Regular
Joined
Apr 28, 2007
Messages
142
Hi
I have a code that open a webpage then copy it and paste on a1
I want to know if there is a way to add a loop that take 10 webpages and paste tham on columns a1-i1
my code:
Sub test()

On Error Resume Next

Dim IE As Object

Set IE = CreateObject("InternetExplorer.Application")


IE.Navigate "CNN International - Breaking News, US News, World News and Video"


Do
If IE.ReadyState = 4 Then
IE.Visible = False
Exit Do
Else
DoEvents
End If
Loop

'Wait for window to open!
Application.Wait (Now + TimeValue("0:00:01"))

IE.Visible = True


IE.ExecWB 17, 0 '// SelectAll

Application.Wait (Now + TimeValue("0:00:01"))



IE.ExecWB 12, 2 '// Copy selection

Application.Wait (Now + TimeValue("0:00:01"))

IE.Quit
' End With
Set IE = Nothing

Application.Wait (Now + TimeValue("0:00:01"))

Range("A1").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", link:=False, _
DisplayAsIcon:=False


Application.CutCopyMode = False



End Sub
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Where do you have the 10 url?
Always paste in A1:I1?
 

silver_axe007

New Member
Joined
Mar 25, 2020
Messages
1
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Could you explain where are the other links?
 

eran3185

Board Regular
Joined
Apr 28, 2007
Messages
142
I have more urls that I want to write them on column t1:t10
I want that the loop will go on those cells
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Always paste in A1:I1?

You did not answer that question.

If it is cell A then use the following macro. But if you should paste into the next available row then use the test2 macro at the end of this post.

VBA Code:
Sub test()
  Dim IE As Object, i As Long
  For i = 1 To 10
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Navigate Range("T" & i).Value
    Do
      If IE.ReadyState = 4 Then
        IE.Visible = False
        Exit Do
      Else
        DoEvents
      End If
    Loop
    
    'Wait for window to open!
    Application.Wait (Now + TimeValue("0:00:01"))
    IE.Visible = True
    IE.ExecWB 17, 0 '// SelectAll
    Application.Wait (Now + TimeValue("0:00:01"))
    IE.ExecWB 12, 2 '// Copy selection
    Application.Wait (Now + TimeValue("0:00:01"))
    IE.Quit
    ' End With
    Set IE = Nothing
    Application.Wait (Now + TimeValue("0:00:01"))
    Range("A1").Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", link:=False, _
    DisplayAsIcon:=False
    Application.CutCopyMode = False
  Next
End Sub

__________________________________________________________________________
VBA Code:
Sub test2()
  Dim IE As Object, i As Long
  For i = 1 To 10
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Navigate Range("T" & i).Value
    Do
      If IE.ReadyState = 4 Then
        IE.Visible = False
        Exit Do
      Else
        DoEvents
      End If
    Loop
    
    'Wait for window to open!
    Application.Wait (Now + TimeValue("0:00:01"))
    IE.Visible = True
    IE.ExecWB 17, 0 '// SelectAll
    Application.Wait (Now + TimeValue("0:00:01"))
    IE.ExecWB 12, 2 '// Copy selection
    Application.Wait (Now + TimeValue("0:00:01"))
    IE.Quit
    ' End With
    Set IE = Nothing
    Application.Wait (Now + TimeValue("0:00:01"))
    Range("A" & Rows.Count).End(xlUp)(2).Select
    ActiveSheet.PasteSpecial Format:="Unicode Text", link:=False, _
    DisplayAsIcon:=False
    Application.CutCopyMode = False
  Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,291
Messages
5,577,223
Members
412,777
Latest member
jmulldome
Top