none
javascript web page to excel RRS feed

  • Question

  • Dear All

    I have excel database of about 100 hyperlinks of webpages like this:

    http://3d-televize.heureka.cz/panasonic-tx-48as640e/porovnat-ceny/#offers

    http://led-televize.heureka.cz/panasonic-tx-42as600e/porovnat-ceny/#offers

    ...

    All ones represent different products but have the same structure.

    I need to have content of these pages in excel. The main purpose is to get the shop and the price.

    So for example:

    SHOP           PRICE

    Mall.cz         15989

    Exasoft.cz    16990

    ...

    I tried web queries but not working on this kind of web page. (error acccured: This Web query returned no data.  To change the query, click OK, click the arrow on the name box in the forumula bar, click the name of the external data range for the Web query, right-click the selection, and then click Edit Query.)

    I went through many scrapers tools (eg. outwit, data miner) but could not find the one manage to solve this issue.

    I am rather basic in VBA but it could be the only solution.

    Is there a way to extract data from those hyperlinks each one in separate sheet to get the needed data?

    Or maybe there is easier way to gather information about Price and Shop for each product and placed it in excel?

    Gordonik


    Wednesday, October 22, 2014 2:43 PM

Answers

  • Hi,

    According to your description, you want to get data from a web site and write into Excel workbook. If Web Query cannot help you achieve the goal, we can resort to msxml2.xmlhttp object in VBA language to access to the elements of a HTML page and then write them into the cells one by one. But that is not related to Excel Object Model and Office development.

    Here is a sample to get the data from the control "comps-results" in a HTML web page and put into Excel. If you have more questions about it, I suggest you posting in VBA forum for more effective responses since it is more related to objects of VBA language.

    Sub GetData()
        Dim x As Long, y As Long
        Dim htm As Object
    
        Set htm = CreateObject("htmlFile")
    
        With CreateObject("msxml2.xmlhttp")
            .Open "GET", "http://www.zillow.com/homes/comps/67083361_zpid/", False
            .send
            htm.body.innerhtml = .responsetext
        End With
    
        With htm.getelementbyid("comps-results")
            For x = 0 To .Rows.Length - 1
                For y = 0 To .Rows(x).Cells.Length - 1
                    Sheets(1).Cells(x + 1, y + 1).Value = .Rows(x).Cells(y).innertext
                Next y
            Next x
        End With
    
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 23, 2014 2:42 AM
    Moderator

All replies

  • Hi,

    According to your description, you want to get data from a web site and write into Excel workbook. If Web Query cannot help you achieve the goal, we can resort to msxml2.xmlhttp object in VBA language to access to the elements of a HTML page and then write them into the cells one by one. But that is not related to Excel Object Model and Office development.

    Here is a sample to get the data from the control "comps-results" in a HTML web page and put into Excel. If you have more questions about it, I suggest you posting in VBA forum for more effective responses since it is more related to objects of VBA language.

    Sub GetData()
        Dim x As Long, y As Long
        Dim htm As Object
    
        Set htm = CreateObject("htmlFile")
    
        With CreateObject("msxml2.xmlhttp")
            .Open "GET", "http://www.zillow.com/homes/comps/67083361_zpid/", False
            .send
            htm.body.innerhtml = .responsetext
        End With
    
        With htm.getelementbyid("comps-results")
            For x = 0 To .Rows.Length - 1
                For y = 0 To .Rows(x).Cells.Length - 1
                    Sheets(1).Cells(x + 1, y + 1).Value = .Rows(x).Cells(y).innertext
                Next y
            Next x
        End With
    
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 23, 2014 2:42 AM
    Moderator
  • Thanks

    I have moved topic to VBA forum

    Thursday, October 23, 2014 8:14 AM