+ Reply to Thread
Results 1 to 20 of 20

VBA Get Function Stopped Working, nothing wrong...

  1. #1
    Registered User
    Join Date
    11-18-2016
    Location
    Seattle, Washington
    MS-Off Ver
    2013
    Posts
    60

    VBA Get Function Stopped Working, nothing wrong...

    Hi,

    This code was working flawlessly, but now it doesn't for some reason. Here's the code:

    Please Login or Register  to view this content.
    The Ticker is "WA/-Kirkland/98034/13405-110th-Pl-NE/185504490", which creates this link:

    http://www.realtytrac.com/property/w...l-ne/185504490

    I don't know why it's not working anymore. Unfortunately, I can't post the HTML code to show what I'm trying to pull. Any help would be greatly appreciated! Thank you!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA Get Function Stopped Working, nothing wrong...

    So the HTML in this link isn't relevant?
    Last edited by Norie; 03-12-2017 at 08:41 PM.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    11-18-2016
    Location
    Seattle, Washington
    MS-Off Ver
    2013
    Posts
    60

    Re: VBA Get Function Stopped Working, nothing wrong...

    Oh it's relevant, I count the < signs to pull the correct data.

    So three >. I don't get why it's not working anymore.

    I just can't post the html code on this forum. It's blocked for whatever reason.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb

    Hi !

    Better is to use the itemprop names as you can read within html webpage code :

    PHP Code: 
    Sub Demo()
        
    With CreateObject("Msxml2.XMLHTTP")
            .
    Open "GET""http://www.realtytrac.com/property/wa/kirkland/98034/13405-110th-pl-ne/185504490"False
            
    .setRequestHeader "DNT""1"
            
    .send
            MsgBox Split
    (Split(.responseText"itemprop='propertyID'>")(1), "<")(0)
        
    End With
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 03-12-2017 at 09:41 PM.

  5. #5
    Registered User
    Join Date
    11-18-2016
    Location
    Seattle, Washington
    MS-Off Ver
    2013
    Posts
    60

    Re: VBA Get Function Stopped Working, nothing wrong...

    Thanks, that kind of works. I want it entered into a cell on the worksheet though, not a message box.Also, I want to pull the purchase date from the webpage as well, but it doens't work with this. Thanks for the help/suggestion though!

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: VBA Get Function Stopped Working, nothing wrong...


    I forgot : your code works on my side ! …

  7. #7
    Registered User
    Join Date
    11-18-2016
    Location
    Seattle, Washington
    MS-Off Ver
    2013
    Posts
    60

    Re: VBA Get Function Stopped Working, nothing wrong...

    Thanks for letting me know! Any idea why it wouldn't work for me?

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: VBA Get Function Stopped Working, nothing wrong...

    • Maybe the request object you use is older than the one in my code …

    • For a cell, just replace MsgBox statement by a cell object reference.

    • As there is no itemprop for the date, start from its text label
    then move to a flag just before desired data (here a class),
    you will need one more Split function …

  9. #9
    Registered User
    Join Date
    11-18-2016
    Location
    Seattle, Washington
    MS-Off Ver
    2013
    Posts
    60

    Re: VBA Get Function Stopped Working, nothing wrong...

    I have no idea how to do that. I reference label class="foreclosureDetailLabel house-tip-a borderedText" for="PurchaseDate" and the date is still 10 lines down, but only requires one more split? It was hard enough to come up with:

    Please Login or Register  to view this content.
    I'll just have to hope that my old way of pulling the date starts to work. Thanks for your help!

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool At beginner level using Split text function …

    PHP Code: 
    Sub Demo2()
        
    With CreateObject("Msxml2.XMLHTTP")
            .
    Open "GET""http://www.realtytrac.com/property/wa/kirkland/98034/13405-110th-pl-ne/185504490"False
            
    .setRequestHeader "DNT""1"
            
    .send
            MsgBox Split
    (Split(Split(.responseText">Purchase Date:<")(1), "<td class=""col2"">")(1), "<")(0)
        
    End With
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

  11. #11
    Registered User
    Join Date
    11-18-2016
    Location
    Seattle, Washington
    MS-Off Ver
    2013
    Posts
    60

    Re: At beginner level using Split text function …

    You're amazing. So it finds purchase date, then it finds the following col2. I was wondering how that worked because there are so many col2's. Can you use this to pull hrefs?

    Like the county website link from this zillow link:

    https://www.zillow.com/homes/13405-1...d-WA-98034_rb/

    The href is within the same <> so can we change the bracketing symbol to "? And find data-za-category="CountyLink"

    And can I donate to you?

    Edit:

    This is how I currently pull the URL:

    Please Login or Register  to view this content.
    Last edited by theskyscraper1; 03-13-2017 at 08:54 PM.

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb href demonstration !


    Just search a close text flag :

    PHP Code: 
    Sub Demo3()
        
    With CreateObject("Msxml2.XMLHTTP")
            .
    Open "GET""https://www.zillow.com/homes/13405-110th-pl-NE-Kirkland-WA-98034_rb/"False
            
    .setRequestHeader "DNT""1"
            
    .send
            MsgBox Replace
    (Split(Split(.responseText"data-za-category=""CountyLink""")(1), """")(1), "amp;""")
        
    End With
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

  13. #13
    Registered User
    Join Date
    11-18-2016
    Location
    Seattle, Washington
    MS-Off Ver
    2013
    Posts
    60

    Re: href demonstration !

    Wow, I can't believe I couldn't find anything about this code prior to using IE. That's awesome. One last question... Eveything you've posted has taught me a lot and allowed me to modify it and do more, but I get stumped when the html is completely different.

    This website has the link names after the href. I want to pull the "Property Search" link and everything prior to the link is ubiquitous. Is it even possible to pull the href from this link?

    http://publicrecords.onlinesearches.com/WA_King.htm

    And seriously, can I donate to you for this help?

  14. #14
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Another href sample : Split of Split is Chrüterchraft ‼


    In fact all is yet in VBA inner help ‼ Just read it and train !

    Here it is just a very basic text manipulation, logic at level of a 10 years old child !
    The purpose is just to extract text after of before some text between some delimiter,
    needing almost warming a couple of brain neurons !

    PHP Code: 
    Sub Demo4()
          
    Dim SP$()
        
    With CreateObject("Msxml2.XMLHTTP")
            .
    Open "GET""http://publicrecords.onlinesearches.com/WA_King.htm"False
            
    .setRequestHeader "DNT""1"
            
    .send
              SP 
    Split(Split(.responseText">Property Search<")(0), "href='")
            
    MsgBox Split(SP(UBound(SP)), "'")(0)
        
    End With
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 03-14-2017 at 06:11 AM.

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Post


    See the thread Expanding the code ? (Get Data from Website) as another sample …

    When webpage code use ID or name better is to use general DOM (Document) functions / properties to scrap data.
    When there is no such ID or name - or for just one data to extract - opt for the VBA basics text functions …

  16. #16
    Registered User
    Join Date
    11-18-2016
    Location
    Seattle, Washington
    MS-Off Ver
    2013
    Posts
    60

    Re: VBA Get Function Stopped Working, nothing wrong...

    Haha, you're amazing and I'm dumb. Is it possible to pull the auditor href from this link?

    https://www.realvantagepoint.com/PIS...edCounty=53033

    Also, is it possible to know if there's an image or not? And if there's data?
    Last edited by theskyscraper1; 03-18-2017 at 03:55 PM.

  17. #17
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: VBA Get Function Stopped Working, nothing wrong...


    Link isn't working by now …

    If desired text to extract appears in responseText the answer is at 99% always yes !

  18. #18
    Registered User
    Join Date
    11-18-2016
    Location
    Seattle, Washington
    MS-Off Ver
    2013
    Posts
    60

    Re: VBA Get Function Stopped Working, nothing wrong...

    That sucks. It seems to need to ping the server. I had to ping the server for a Maricopa County downloader I made. The previous link was a county href link from:

    https://www.realvantagepoint.com/PIS...x?S=Washington

    That country link is:

    https://www.realvantagepoint.com/PIS...t/Landing.aspx

    Any ideas why it's doesn't work without cache?

  19. #19
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: VBA Get Function Stopped Working, nothing wrong...


    It's like a protection … So you can pilot IE instead of a direct request.

  20. #20
    Registered User
    Join Date
    11-18-2016
    Location
    Seattle, Washington
    MS-Off Ver
    2013
    Posts
    60

    Re: VBA Get Function Stopped Working, nothing wrong...

    I figured out a way to ping realvantage. Could you please help me with my code? This is what I have:

    Please Login or Register  to view this content.
    I want to pull the assessors link from this source code:

    beQXEQs.jpg

    Or imgur link:

    http://i.imgur.com/beQXEQs.jpg

    Please help me. I've done a lot of work already and really need this to work. I'm going crazy. Thank you!

+ 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. [SOLVED] Formula stopped working - what's wrong ?
    By Lukael in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-12-2016, 01:13 PM
  2. Colour function stopped working after one calculation
    By KatD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-07-2016, 06:29 PM
  3. [SOLVED] Vlookup function suddenly stopped working!!!!!
    By danmack in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-21-2013, 10:39 AM
  4. MSNStockQuote function stopped working - but only for the Microsoft ticker symbol
    By y2kcrackers2989 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-21-2012, 01:45 PM
  5. [SOLVED] Excel 2007 : Cut & paste function stopped working - no idea why!
    By cfoxy in forum Excel General
    Replies: 4
    Last Post: 04-11-2012, 05:57 PM
  6. Paste function stopped working
    By sans in forum Excel General
    Replies: 6
    Last Post: 04-06-2012, 01:46 PM
  7. Paste Function Stopped Working.
    By modytrane in forum Excel General
    Replies: 5
    Last Post: 02-17-2011, 12:15 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