excel - VBA Script pull data from website -
i want pull data http://www.buyshedsdirect.co.uk/ recent prices of specific items.
i have excel spreadsheet following:
|a | b 1 |item |price 2 |bfd/garden-structures/arches/premier-arches-pergola
and vba script:
dim ie new internetexplorer dim item string item = sheet1.range("a2").value dim doc htmldocument ie.visible = true ie.navigate "http://www.buyshedsdirect.co.uk/" & item doevents loop until ie.readystate = readystate_complete set doc = ie.document on error resume next output = doc.getelementbyclass("nowvalue").innertext sheet1.range("b2").value = output ie.quit end sub
i new vba scripting , have no idea why isnt pulling value form class "nowvalue"
any appreciated :)
the on error resume next
line stopping error message being displayed. error message there no method on htmldocument called "getelementbyclass". want "getelementsbyclassname" instead , have handle fact returns collection rather single element. code work:
option explicit sub foo() dim ie new internetexplorer dim item string item = sheet1.range("a2").value dim doc htmldocument ie.visible = true ie.navigate "http://www.buyshedsdirect.co.uk/" & item doevents loop until ie.readystate = readystate_complete set doc = ie.document dim results ihtmlelementcollection dim result ihtmlelement dim output string set results = doc.getelementsbyclassname("nowvalue") output = "" each result in results output = output & result.innertext next result sheet1.range("b2").value = output ie.quit end sub
you find there multiple elements class "nowvalue" on page. looks though 1 want might enclosed in div called "variantprice" code should work:
option explicit sub bar() dim ie new internetexplorer dim item string item = sheet1.range("a2").value dim doc htmldocument ie.visible = true ie.navigate "http://www.buyshedsdirect.co.uk/" & item doevents loop until ie.readystate = readystate_complete set doc = ie.document dim results ihtmlelementcollection dim results2 ihtmlelementcollection dim result ihtmlelement dim result2 ihtmlelement dim output string set results = doc.getelementsbyclassname("variantprice") output = "" each result in results set results2 = result.getelementsbyclassname("nowvalue") each result2 in results2 output = output & result2.innertext next result2 next result sheet1.range("b2").value = output ie.quit end sub
edit: code above works me fails work question asker, may case using older version of internet explorer not support getelementsbyclassname
. may case using queryselector
work instead. certain, go this quirksmode page determine browser supports.
new code using queryselector
:
option explicit sub bar() dim ie new internetexplorer dim doc htmldocument dim result ihtmlelement dim result2 ihtmlelement dim item string item = sheet1.range("a2").value ie.visible = true ie.navigate "http://www.buyshedsdirect.co.uk/" & item doevents loop until ie.readystate = readystate_complete set doc = ie.document set result = doc.queryselector(".variantprice") set result2 = result.queryselector(".nowvalue") sheet1.range("b2").value = result2.innertext ie.quit end sub
further edit: make macro loop through of entries in column a, here relevant bits add or change:
option explicit sub bar() dim ie new internetexplorer dim doc htmldocument dim result ihtmlelement dim result2 ihtmlelement dim item string dim lrow long ie.visible = true lrow = 2 item = sheet1.range("a" & lrow).value until item = "" ie.navigate "http://www.buyshedsdirect.co.uk/" & item doevents loop until ie.readystate = readystate_complete set doc = ie.document set result = doc.queryselector(".variantprice") set result2 = result.queryselector(".nowvalue") sheet1.range("b" & lrow).value = result2.innertext lrow = lrow + 1 item = sheet1.range("a" & lrow).value loop ie.quit end sub
Comments
Post a Comment