+ Reply to Thread
Results 1 to 21 of 21

VBA web data scraping from table

  1. #1
    Registered User
    Join Date
    08-25-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    6

    VBA web data scraping from table

    Please Login or Register  to view this content.
    scr.JPG

    I have a value in cell A2 = 03-3114, code above is searching website for that input and returns with results in a table
    I am trying to scrape that data back into excel cell, I tried few different ways but so far I have not found working solution

    Search for serial 03-3114 returns type = C-17A (third field in the table)
    I'd like to import that value into my table in to cell B2,

    I was able to find xPath using Firefox Firebug but I dont know what to do next

    /html/body/div[1]/div/div[2]/div/div[2]/div[5]/div[2]/div[1]/table/tbody/tr[3]/td[3]/span


    any help is appreciated.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,906

    Re: VBA web data scraping from table

    Fotodj,

    This may not be the ideal solution since it is quite static in where it looks for the search result, but with current page layout seems to work. It might technically be better to loop through each row/cell of the table, look for your search value and then get the 2nd cell after that. However, try this simpler code below. Note I added a 5 second pause... you may or may not need it, but for some reason I kept getting an error even though IE.ReadyState was 4, and not busy.... It still couldn't find the object until I added the delay. This code would go after your for loop.

    Please Login or Register  to view this content.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    08-25-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    6

    Re: VBA web data scraping from table

    Please Login or Register  to view this content.
    Thank you, Arkadi

    Modified code above works for me even with 2 sec delay

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: VBA web data scraping from table

    Please Login or Register  to view this content.

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,156

    Re: VBA web data scraping from table

    Please Login or Register  to view this content.

  6. #6
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: VBA web data scraping from table

    Hi Kyle,
    Much better than IE
    Where did you get this line

    "Itemid=60&af=usaf&serial=" & searchTerm & "&sbm=Search&code=&searchtype=&unit=&cn="
    ?

  7. #7
    Forum Contributor IonutC's Avatar
    Join Date
    01-28-2016
    Location
    Bucharest, Romania
    MS-Off Ver
    Office 2019
    Posts
    486

    Re: VBA web data scraping from table

    Guys,

    How you made your text to be green?
    Please consider:
    Be polite. Thank those who have helped you.
    Click the star icon in the lower left part of the contributor's post and add Reputation. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Posting code between tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,156

    Re: VBA web data scraping from table

    @AB33 open the network panel on developer tools when making in the search request and have a look in the request body.

  9. #9
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,156

    Re: VBA web data scraping from table

    @IonutC - A Chrome extension I wrote https://chrome.google.com/webstore/d...nacjapndgmjdoe

  10. #10
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: VBA web data scraping from table

    Hi Kyle,
    Ta!
    A couple of questions:
    Are these parameters for the post method? If so, do we need to include all of them as you have done?
    Can the code work with late binding (HTMLDocument)?
    Last edited by AB33; 08-25-2016 at 10:51 AM.

  11. #11
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,156

    Re: VBA web data scraping from table

    No problem

    Erm, it depends on the website. Some will need them all, others will be happy with just a couple - it's more resilient to include them all.

    No, the code won't work with late binding - you can't select by class name using late binding. You could re-write that bit, but you'd need to loop through as per the other solutions.

  12. #12
    Forum Contributor IonutC's Avatar
    Join Date
    01-28-2016
    Location
    Bucharest, Romania
    MS-Off Ver
    Office 2019
    Posts
    486

    Re: VBA web data scraping from table

    Hi there is not working the syntax color extension

  13. #13
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,156

    Re: VBA web data scraping from table

    You're hijacking the thread, if you've got any queries on the extension, have a look here http://www.excelforum.com/the-water-...extension.html

  14. #14
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: VBA web data scraping from table

    Hi Kyle,
    Ta again!

  15. #15
    Registered User
    Join Date
    08-25-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    6

    Re: VBA web data scraping from table

    Please Login or Register  to view this content.
    how should I change that line to have the value imported from Sheets("Scramble").Range("B2").Value, I am getting "constant expression required" error ?

  16. #16
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: VBA web data scraping from table

    Dim searchTerm As String
    Then

    searchTerm = Sheets("Scramble").Range("B2").Value

  17. #17
    Registered User
    Join Date
    08-25-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    6

    Re: VBA web data scraping from table

    Please Login or Register  to view this content.
    @AB33 I added Dim searchTerm As String
    , still getting the same error, see modified code

  18. #18
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: VBA web data scraping from table

    Make sure that the value comes from the right cell. I have it mine on A2

    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    08-25-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    6

    Re: VBA web data scraping from table

    @AB33 - my error, everything works perfect. Thank you for your help! I learned at lot today.

    Another small improvement, if I have few values in column A , how can I loop through column A, so the code
    brings data to corresponding rows in column C in one run?

  20. #20
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: VBA web data scraping from table

    Well, credit should get to Kyle. I am only polishing it.
    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    08-25-2016
    Location
    New York
    MS-Off Ver
    2013
    Posts
    6

    Re: VBA web data scraping from table

    @Kyle123 - AB33 is working with me on the code which you came up with, I really appreciate your help, thank you!
    @AB33 Big thanks, loop is working like a charm:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Web Scraping Data Using VBA
    By zaska in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-25-2017, 09:52 PM
  2. Scraping/Importing Table
    By Madskillet in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2016, 07:34 PM
  3. Scraping data and pre-processing in one go?
    By xteejx in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-23-2014, 02:41 PM
  4. scraping data from a website
    By redpanda in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-05-2012, 01:20 PM
  5. Scraping Data using VBA
    By Nala2355 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-16-2011, 11:21 AM
  6. Data scraping
    By brewers7 in forum Excel General
    Replies: 8
    Last Post: 02-06-2011, 12:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1