1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Web scrapping To Excel using VBA

Discussion in 'VBA Macros' started by rajesh2014, Jul 14, 2014.

  1. rajesh2014

    rajesh2014 Member

    Messages:
    39
    I am trying to scrape particular information from the below website. I have tried by recording macros, but i am not getting output for this particular website only.

    Website name : http://xgis.maaamet.ee/ky/FindKYByT.asp

    I have two column in my Excel sheet. Column A have some parcel numbers and Column B is Area.

    If i entered parcel number like 41201:004:0068, i need to get 3900 m² in Column B

    Is that possible.
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,724
    If you know the math you are better to replicate the web site logic in Excel
    Do you know what te logic is ? or even a description in English would help us help you

    Do Google Earth or Bling offer a similar output/service ?
    Last edited: Jul 14, 2014
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,724
    Reading the documentation at the web site I think if you contact the author (Section 5) of the manual he may be able to assist with an Excel or direct web link

    "5. Support
    If you have any problems or questions regarding the use of Quick Query, please send an e-mail with the description of problem or question to kaardirakendus@maaamet.ee or call..."
  4. Marc L

    Marc L Excel Ninja

    Messages:
    4,265

    Hi rajesh !

    In the documentation is written how to directly load the parcel page, just read it !

    To grab data, read the VBA inner help of QueryTable object for example
    or any other ways in this forum or all over the web !
  5. rajesh2014

    rajesh2014 Member

    Messages:
    39
    Thanks Hui and Marc,

    I have tried by recording macros using the manual.

    Code (vb):

    With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://xgis.maaamet.ee/ky/vorm", Destination:=Range("$A$1"))
            .Name = "vorm"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlSpecifiedTables
            .WebFormatting = xlWebFormattingNone
            .WebTables = "2"
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        Range("B1").Select
     
    When i was recording macro, i entered URL as "http://xgis.maaamet.ee/ky/FindKYByT.asp?txtCU=72704:004:0430", but in macro its showing as "http://xgis.maaamet.ee/ky/vorm"

    i need to change txtCU everytime, so by using this code, i can't change it.

    Any idea?
  6. Marc L

    Marc L Excel Ninja

    Messages:
    4,265

    Ok, bad way …

    So you have to search for pilot IE way …
  7. rajesh2014

    rajesh2014 Member

    Messages:
    39
    Sorry to ask you, what is that Pilot IE?
  8. Marc L

    Marc L Excel Ninja

    Messages:
    4,265

    Means to pilot Internet Explorer …

    Other web search : microsoft XMLHTTP …

    But for a noob the easy way is to follow Hui's post #3 !
  9. rajesh2014

    rajesh2014 Member

    Messages:
    39
    I contacted, but not received any response, i will search microsoft XMLHTTP,
  10. rajesh2014

    rajesh2014 Member

    Messages:
    39
    Hi

    I have written some code using XML HTTP, still i am getting error as "Table not found". Here is my code.

    Code (vb):

    Sub AdditionalInfo()
        Dim oDom As Object: Set oDom = CreateObject("htmlFile")
        Dim x As Long, y As Long
        Dim oRow As Object, oCell As Object
        Dim data, aTable As Object
       
        y = 1: x = 1
       
        With CreateObject("msxml2.xmlhttp")
            .Open "GET", "http://xgis.maaamet.ee/ky/FindKYByT.asp?txtCU=72704:004:0430", False
            .send
            oDom.body.innerHTML = .responseText
        End With
       
        For Each aTable In oDom.getElementsByTagName("table")
            If Trim(LCase(aTable.className)) = "tblPager" Then
                With aTable
                    ReDim data(1 To .Rows.Length, 1 To .Rows(1).Cells.Length)
                    For Each oRow In .Rows
                        For Each oCell In oRow.Cells
                            data(x, y) = oCell.innerText
                            y = y + 1
                        Next oCell
                        y = 1
                        x = x + 1
                    Next oRow
                End With
                Exit For
            End If
        Next aTable
       


        With Sheets(2).Cells(1, 1)
            .CurrentRegion.ClearContents
            If IsArray(data) Then
                .Resize(UBound(data), UBound(data, 2)).Value = data
            Else: MsgBox "Table not found"
            End If
        End With


    End Sub
     
  11. Marc L

    Marc L Excel Ninja

    Messages:
    4,265

    Code looks good but web page seems to be written in CSS style,
    more difficult to grab data than normal pages …​
    Last edited: Jul 18, 2014
  12. Marc L

    Marc L Excel Ninja

    Messages:
    4,265

    In fact if you kwow how to use your web browser, you can see the request called like this :

    http://xgis.maaamet.ee/ky/vorm?xVRM.AASave=Otsi&F3X7tNFk=72704:004:0430&FVc3zIa6=&FMu0oiRJ=&FFUHGuoP=

    But bold parts change at each request, like a time protection I guess …

    Using IE is no more a pleasure : Ajax type webpage with cascade of dynamic iFrames, lost in dedalus !

    Maybe you could take a glance at a webdriver wrapper like Selenium v2, no guarantee but an easy way …

Share This Page