locked
VBA code data extraction RRS feed

  • Question

  • I use below mentioned code but there is no out put for price and seller info data.If anyone have idea then please change code for seller,price data extraction from html code.thanks

    Sub RunNewModule()
    
    Dim new change as char
    
    
    End Sub



    Tuesday, December 1, 2015 6:31 AM

Answers

  • Basically, it's like this.


    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    
    With Sheets("Source")
    
       RowCount = 2
       Do While .Range("A" & RowCount) <> ""
          CellName = .Range("A" & RowCount)
          url = CellName ' LOOP THROUGH URLs 
    
          'get web page
          IE.Navigate2 url
          Do While IE.readyState <> 4 Or _
             IE.Busy = True
             DoEvents
          Loop
          
          ' YOUR LOGIC HERE . . . 
    
        Sheets("Source").Select
        RowCount = RowCount + 1
        
        Loop
        
    End With
    IE.Quit



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by tahirsatti123 Wednesday, December 9, 2015 4:26 AM
    Tuesday, December 8, 2015 5:56 PM

All replies

  • Here are a few things wrong:

    1. Move Cells.clear to the beginning.  You output the price then clear the price.

    2. Move Set ie = Nothing after ie.Quit.

    3. olpOfferPrice is just the price it is doesn't have the seller info.  You need to extract the seller info in another class. 

    • Proposed as answer by ryguy72 Wednesday, December 2, 2015 4:17 AM
    Tuesday, December 1, 2015 4:43 PM
  • Are you trying to do something like this?

    Sub DumpData()
    
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    
    URL = "http://www.amazon.com/gp/offer-listing/B00NNZZG1W/ref=olp_f_new?ie=UTF8&f_new=true&f_primeEligible=true"
    
    'Wait for site to fully load
    IE.Navigate2 URL
    Do While IE.Busy = True
       DoEvents
    Loop
    
    RowCount = 1
       
    With Sheets("Sheet1")
       .Cells.ClearContents
       RowCount = 1
       For Each itm In IE.document.all
            If itm.classname = "a-row a-spacing-mini olpOffer" Then
                .Range("A" & RowCount) = itm.classname
                .Range("B" & RowCount) = Left(itm.innertext, 1024)
            End If
            RowCount = RowCount + 1
       Next itm
    
    End With
    
    Columns("A:B").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A2190" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B1000000")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
        
    End Sub
    
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, December 2, 2015 7:24 PM
  • Basically, it's like this.


    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    
    With Sheets("Source")
    
       RowCount = 2
       Do While .Range("A" & RowCount) <> ""
          CellName = .Range("A" & RowCount)
          url = CellName ' LOOP THROUGH URLs 
    
          'get web page
          IE.Navigate2 url
          Do While IE.readyState <> 4 Or _
             IE.Busy = True
             DoEvents
          Loop
          
          ' YOUR LOGIC HERE . . . 
    
        Sheets("Source").Select
        RowCount = RowCount + 1
        
        Loop
        
    End With
    IE.Quit



    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by tahirsatti123 Wednesday, December 9, 2015 4:26 AM
    Tuesday, December 8, 2015 5:56 PM