Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    I'm trying to geocode addresses in a database. I'm successfully getting a json output from Google, but being new to Regular Expressions, am having difficulty building my search patterns to extract the information I need. So far I've been successful in retrieving the Google status, but I'm concerned about the number of spaces that may or may not be present between "status": and "OK". My current pattern assumes one space and I don't know how to modify it to handle zero to multiple spaces. Following is my code, its output and a snip-it from the json result.

    Debug.Print
    Debug.Print "TEST ...."

    stPattern = "status" & """" & ": " & """" & "(.+)" & """" & ","

    Debug.Print "Pattern: " & st Pattern
    Debug.Print "Result: " & Rematch( _
    rest!response, _
    st Pattern _
    )
    Debug.Print rest!response


    TEST ....
    Pattern: status": "(.+)",
    Result: OK


    Goggle json snip-it ....

    {
    "status": "OK",
    "results": [ {

    Thanks for your assistance,
    Marty



  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by mcowen View Post
    I'm successfully getting a json output from Google, but being new to Regular Expressions, am having difficulty building my search patterns to extract the information I need. So far I've been successful in retrieving the Google status, but I'm concerned about the number of spaces that may or may not be present between "status": and "OK".
    First, you might want to search around for a class or at least a function that reads JSON notation in VB. That would save a lot of manual parsing.

    What do you think about using the Split() method? It appears that the first level separator is the comma, and the second level separator is a colon. So this should be the status:

    Code:
    strStatus = Replace(Trim(Split(Trim(Split(strJSON, ",")(0)), ":")(1)), Chr(34), "")
    Of course, this breaks down as you get arrays of arrays, so I think you will want to find some reliable code for reading JSON.

    What is the host application? You might be able to use JavaScript.

    == Edit ==

    These first two were listed on json.org, and the third popped up elsewhere. I haven't tried them myself.



    == Edit ==

    I incorporated the VB-JSON code into a Word document for testing in VBA. Attached if you're interested. Note the References I had to add to get it to compile.

    [attachment=88356:Sample.doc]
    Attached Files Attached Files

  4. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thanks for the input. I'm building the geocoding functionality with in an Access 2007 db. I thought about using doing my own parsing, but except for the first status entry, it seemed to get very complex very fast to find other values in the json output. Your word Sample.doc looks very interesting and I'll explore its use in detail. Thanks for the other suggestions as well. The first two are beyond my know-how in adapting them for VBA use. The third one looked promising, but I can't figure out how to download the read-only version.

    Marty


  5. #4
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    After lots of trial & error I found a solution to handling none, one or many blanks. In the ": " portion of the expression I added a ? after the blank, making the blank optional.

    stPattern = "status" & """" & ": ?" & """" & "(.+)" &

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •