• 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...

  • 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

R

rajesh2014

Member
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.
 
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:
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..."
 

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 !
​
 
Thanks Hui and Marc,

I have tried by recording macros using the manual.

Code: 
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?
 

Means to pilot Internet Explorer …

Other web search : microsoft XMLHTTP …

But for a noob the easy way is to follow Hui's post #3 !
​
 
Hi

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

Code: 
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
 

Code looks good but web page seems to be written in CSS style,
more difficult to grab data than normal pages …​
 
Last edited:

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 …
​
 
You must log in or register to reply here.
Back
Top