+ Reply to Thread
Results 1 to 2 of 2

Web Scrapping Macro stopped working

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Web Scrapping Macro stopped working

    Hi All,

    I have a macro which fetches data from www.Eppraisal.com. But unfortunately now it's no more working. I think it is because some changes happened in the website.

    Following is the Function from macro code which has primary role:

    Public Function GetEppraisalValuation(ByRef wrkshtPI As Worksheet, ByVal intRowOffset As Long, ByVal strAddressForWeb1 As String, ByVal strAddressForWeb2 As String)
        
        Dim strURL As String
        Dim strLocationURL As String
        
        Dim strEppraisalValue As String
        Dim strEppraisalHighLow As String
        
        Dim lngStartPointer As Long
        Dim lngEndPointer As Long
    
    
        strURL = "http://www.eppraisal.com/Search/Property.mvc?a=" & modUtility.URLEncode(strAddressForWeb1) & "&z=" & modUtility.URLEncode(strAddressForWeb2)
        'DeleteUrlCacheEntry (strURL)
        
        'ThisWorkbook.Worksheets("Sheet1").Range("G1") = strURL
        
        zHttp.Open "GET", strURL, False
        zHttp.Option(iWinHttpRequestOption_EnableRedirects) = False
        zHttp.setRequestHeader "Accept", "text/html, application/xhtml+xml, */*"
        zHttp.setRequestHeader "Accept-Encoding", "gzip, deflate"
        zHttp.setRequestHeader "Accept-Language", "en-us"
        zHttp.setRequestHeader "User-Agent", "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)"
        zHttp.setRequestHeader "Referer", strURL
        zHttp.setRequestHeader "Connection", "Keep-Alive"
        zHttp.setRequestHeader "Host", "www.eppraisal.com"
        'zHttp.setRequestHeader "Cookie", "ASP.NET_SessionId=" & SessionID
        'zHttp.setRequestHeader "Cookie", ".ASPXAUTH=" & ASPXAUTH
    
        zHttp.send
        
        If zHttp.Status <> 302 Then
            Exit Function
        End If
        
        strLocationURL = zHttp.getResponseHeader("Location")
            
        strURL = "http://www.eppraisal.com" & strLocationURL
        'DeleteUrlCacheEntry (strURL)
        
        zHttp.Open "GET", strURL, False
        zHttp.Option(iWinHttpRequestOption_EnableRedirects) = True
        zHttp.setRequestHeader "Accept", "text/html, application/xhtml+xml, */*"
        'zHttp.setRequestHeader "Accept-Encoding", "gzip, deflate"
        zHttp.setRequestHeader "Accept-Language", "en-us"
        zHttp.setRequestHeader "User-Agent", "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)"
        zHttp.setRequestHeader "Referer", strURL
        zHttp.setRequestHeader "Connection", "Keep-Alive"
        zHttp.setRequestHeader "Host", "www.eppraisal.com"
        'zHttp.setRequestHeader "Cookie", "ASP.NET_SessionId=" & SessionID
        'zHttp.setRequestHeader "Cookie", ".ASPXAUTH=" & ASPXAUTH
    
        zHttp.send
        
        If zHttp.Status <> 200 Then
            Exit Function
        End If
        
        ieDom.body.innerHTML = zHttp.responseText
        For Each ieInp In ieDom.getElementsByTagName("p")
            If ieInp.className = "ColorAccent6 FloatLeft FontSizeK Margin0" Then
                strEppraisalValue = ieInp.innerText
                Exit For
            End If
        Next
    
        For Each ieInp In ieDom.getElementsByTagName("p")
            If ieInp.className = "FontSizeA FloatRight Margin0 DisplayNone HighLow" Then
                strEppraisalHighLow = ieInp.innerText
                Exit For
            End If
        Next
    
        wrkshtPI.Range("C1").Offset(intRowOffset, 0) = strEppraisalValue
        
        lngStartPointer = InStr(1, strEppraisalHighLow, "Low:")
        If lngStartPointer = 0 Then
            Exit Function
        End If
        lngEndPointer = InStr(1, strEppraisalHighLow, Chr(10))
        If lngEndPointer = 0 Then
            Exit Function
        End If
        wrkshtPI.Range("D1").Offset(intRowOffset, 0) = Trim(Mid(strEppraisalHighLow, lngStartPointer + 4, lngEndPointer - (lngStartPointer + 5)))
        wrkshtPI.Range("E1").Offset(intRowOffset, 0) = Trim(Mid(strEppraisalHighLow, lngEndPointer + 7, Len(strEppraisalHighLow) - (lngEndPointer + 7)))
    End Function
    If any web scrapping expert can have a look at it and tell me what is going wrong, it would be a BIG help for me.

    Thanks In Advance

  2. #2
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Web Scrapping Macro stopped working

    Is there any Web Scrapping expert to help me out please?

+ 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. Macro Stopped Working
    By andybason in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-11-2012, 01:20 PM
  2. Macro stopped working.
    By joleen in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-06-2011, 05:37 AM
  3. Excel 2007 : Macro stopped working
    By jaw0001 in forum Excel General
    Replies: 5
    Last Post: 10-13-2010, 07:59 AM
  4. Excel 2007 : Macro Code Stopped Working
    By stevo_300 in forum Excel General
    Replies: 5
    Last Post: 04-06-2010, 07:57 AM
  5. Macro has stopped working after date change
    By markrennolds in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-01-2009, 08:31 PM

Tags for this Thread

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