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

Popular posts from this blog

powershell Start-Process exit code -1073741502 when used with Credential from a windows service environment -

i'm running strange behavior powershell start-process call. here call: $process = start-process ` "c:\somepath\mybinary.exe" ` -passthru ` -credential $defaultcredential ` -wait ` -workingdirectory "c:\somepath" ` -loaduserprofile if ($process.exitcode -ne 0) { #do } this call return exit code - 1073741502 . after quick search, exit code seems related generic error when program not load required dll (aka. status_dll_init_failed ). when run without -credential $credential program runs correctly. in order isolate problem, manually launched some.exe in prompt target credential , runs smoothly. so problem seems come way start-process cmdlet launch process. i found potential solutions problem tried apply no luck : link , link . would have idea of what's going on here ? edit 1: run proc mon monitoring program activities when launched directly or via powershell script. problem seems occur when loading kernelbase
Read more

twig - Using Twigbridge in a Laravel 5.1 Package -

i'm trying create laravel 5.1 package uses twigbridge instead of .php views. can normal .php views display fine, when try make use of .twig templates i'm met error. possible use twigbridge templates when creating views laravel package or pretty stuck blade , php ? edit 1: loading twig template without extending works. it's when start using {% extends 'file' %} error comes out. i error error loading /virtual/laravel/packages/username/mypackage/src/views/sample.twig: template "templates.master" not defined () in "/virtual/laravel/packages/username/mypackage/src/views/sample.twig" @ line 1. my folders username |_ mypackage |_ src |_ controllers - samplecontroller.php |_ views |_ templates - master.twig - sample.twig twig template sample.twig {% extends 'templates.master' %} {% block content %} {{ parent()
Read more

c# - LINQ join Entities from HashSet's, Join vs Dictionary vs HashSet performance -

i have hashset's each stores t, have written test application compares different relation algorithms can think of, i'm not pleased results getting. does there exist more efficient ways of achieving object relations, once testing? using system; using system.collections.generic; using system.linq; using system.text; using system.threading.tasks; using system.diagnostics; namespace linqtests { class program { static void main(string[] args) { hashset<user> usertable = new hashset<user>(); hashset<userproperty> userpropertytable = new hashset<userproperty>(); #region lets create dummy data. console.writeline("please wait while creating dummy data, can take while..."); console.writeline(""); int rows = 1000000; for(int x = 0; x < rows; x++) { random rnd = new random(); // ad
Read more