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