+ Reply to Thread
Results 1 to 3 of 3

Extract a Specific data from multiple webpages to excel

  1. #1
    Registered User
    Join Date
    03-06-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    17

    Extract a Specific data from multiple webpages to excel

    I have to check whether a page has review or not.

    Web page : http://www.junglee.com/dp/B00BKZPUUE
    Review :No

    Web page : http://www.junglee.com/dp/B0000251W5
    Review : Yes

    I have to do this manually for 1000 pages everyday, I just have to check if review is present or not.

    Is there any way I can automate the process by using VBA , So that my excel sheet fills automatically with "Yes" and "No" in the "Review" Section.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-15-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Extract a Specific data from multiple webpages to excel

    Hi Pari,
    Here's the code for you. This function returns True/False depending on the link having reviews or otherwise. I am using the fact that the web page has "Be the first to review" for No reviews and "Write a review" for pages with reviews.
    If you'd like you could pass sLinkToCheck as a parameter to this function from outside. Also, you could make slight modifications to make it a loop to check multiple links.
    Also you need to go to Tools->References in VBA and check "Microsoft Internet Controls" for this to work.
    Regards
    Arun Madhavan

    Function DoesLinkHaveReview() As Boolean
    Dim appIE As SHDocVw.InternetExplorer
    Set appIE = New SHDocVw.InternetExplorer
    Dim varTables, varTable
    Dim varRows, varRow
    Dim varCells, varCell
    Dim lngRow As Long, lngColumn As Long
    Dim lnk As Object
    Dim i As Long
    Dim lNum As Long
    Dim sLinkToCheck As String

    'sLinkToCheck = "http://www.junglee.com/dp/B00BKZPUUE"
    sLinkToCheck = "http://www.junglee.com/dp/B0000251W5"

    'OPEN INTERNET EXPLORER, GO TO WEBPAGE
    appIE.Visible = True
    appIE.navigate sLinkToCheck
    Do While appIE.Busy: DoEvents: Loop
    Do While appIE.readyState <> 4: DoEvents: Loop

    'POPULATE USER FIELDS, AND PERFORM A QUERY

    Do While appIE.Busy: DoEvents: Loop
    Do While appIE.readyState <> 4: DoEvents: Loop
    appIE.Document.all.Item
    Do While appIE.Busy: DoEvents: Loop
    Do While appIE.readyState <> 4: DoEvents: Loop
    'PARSE THROUGH WEBPAGE, EXTRACT TEXT TO WORKSHEET
    For Each lnk In appIE.Document.Links
    If InStr(1, lnk.innerhtml, "Be the first to review") > 0 Then
    DoesLinkHaveReview = False
    Debug.Print "Link does not have review"
    appIE.Quit
    Exit Function
    End If
    If InStr(1, lnk.innerhtml, "Write a review") > 0 Then
    DoesLinkHaveReview = True
    Debug.Print "Link has reviews"
    appIE.Quit
    Exit Function
    End If
    Next

    appIE.Quit

    End Function

  3. #3
    Registered User
    Join Date
    03-06-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: Extract a Specific data from multiple webpages to excel

    Thanks a lot for the help, But I have no idea how to edit the code for my needs, as I don't know anything about codes.
    It will be of great help if you could edit the same for n no of links so that it gives the TRUE or FALSE message , and my default browser is Mozilla Firefox.

    Thanks.
    Attached Files Attached Files
    Last edited by pari9485; 09-05-2013 at 04:15 AM.

+ 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. Getting data from multiple webpages and posting into one excel sheet
    By Ashima93 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 06-19-2013, 08:31 AM
  2. Extract specific column data from specific sheet from multiple files in a folder
    By piggyfox in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-31-2013, 11:51 AM
  3. Scraping Data from tables in multiple webpages
    By heiyan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2013, 10:35 AM
  4. Extract data from cells in specific worksheets in multiple workbooks
    By bm97sph in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2013, 08:22 AM
  5. Macro to extract data from multiple workbooks, specific sheet, specific cells
    By crissandraauree in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2012, 03:54 PM

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