dimanche 19 avril 2015

VBA Webscrape Loop

I have a code below that will scrape all the info from a page for whatever one ticker I put into A2. I use a vlookup separately to put a statistic in a cell next to it, then copy and paste it into another area so not to mess with the vlookup (kind of innificient, I know, but not the question.)

I have a second stock ticker in A3 I want to do the same thing for...but I don't know how to properly loop the code for A3. The code currently just does it twice for A2. (This problem probably has to do with the my_page line of code sticking to A2? )

Anyways, I just want the code to spit out something. copy/paste what I want. Then go to line two, spit out the webpage. copy/paste in the row under the first. and so on.

Any advice on how to loop by row?

Sub Macro1()

Dim rng As Range
Dim row As Range
Dim cell As Range

Set rng = Range("A2:A3")

For Each row In rng
For Each cell In row.Cells

my_Page = "http://ift.tt/Jz88mB" & Range("A2").Value
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
.Navigate my_Page
Do Until .ReadyState = 4: DoEvents: Loop
End With

Application.EnableEvents = False
IE.ExecWB 17, 0
Do Until IE.ReadyState = 4: DoEvents: Loop
IE.ExecWB 12, 2
Sheets("Sheet1").PasteSpecial Format:="HTML", link:=False,
DisplayAsIcon:=False, NoHTMLFormatting:=True

Application.EnableEvents = True

Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone_
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Next cell
Next row

End Sub

Aucun commentaire:

Enregistrer un commentaire