1

I've an excel workbook that looks something like this:

/-------------------------------------\
|   Lat    |   Long   |     Area      |
|-------------------------------------|
| 5.3      | 103.8    |   AREA_NAME   |
\-------------------------------------/

I also have a JSON api with a url of the following structure:

https://example.com/api?token=TOKEN&lat=X.X&lng=X.X

that returns a JSON object with the following structure:

{ "Area": "AREA_NAME", "OTHERS": "Other_details"}

I tried to implement a VBA function that will help me to extract AREA_NAME. However, I keep getting syntax errors. I don't know where I am going wrong.

Function get_p()
    Source = Json.Document (Web.Contents("https://example.com/api?token=TOKEN&lat=5.3&lng=103.8"))
    name = Source[Area]
    get_p = Name
End Function

I intentionally hardcoded the lat and long value for development purposes. Eventually, I want the function to accept lat and long as parameters. I got the first line of the function from PowerQuery Editor.

Where am I going wrong? How to do this properly in VBA? Or is there a simpler way using PowerQuery?

Mohideen Imran Khan
  • 833
  • 6
  • 25
  • Do you mind including the declaration of the variables in your code? What is `Source` dimmed as and - more importantly - what is `name` supposed to be? One- or two-dimensional arrays of type `String`? Collections or dictionaries? ListObjects? I guess with `ListObjects` you might even be able to use `Source[Area]`. – Ralph Dec 01 '16 at 11:26
  • @Ralph I didn't declare the variable type. I just started VBA and have not explicitly declared types before, and that has not been a problem so far. Is that important in this case? – Mohideen Imran Khan Dec 01 '16 at 13:30
  • Yes, because you want `Source` to be a certain data type (or in this case object) which organizes the data for you so that you can afterwards reference `[Area]` within. If you don't declare any of your variables then the VBE will simply declare `Source` as `Variant` and (depending on your first use of the variable) change the type to the final type (while running the code). I am guessing that VBE decides to make `Source` a `String`. Hence, the next line `Source[Area]` doesn't make any sense. Instead you'll have to parse the resulting string (with comma delimiters). – Ralph Dec 01 '16 at 13:53
  • Essentially, you need to (in a first step) change JSON to a format which is legible and recognizable by Excel. So, you need to convert JSON to an object which Excel is used to, such as a table or ListObject or dictionary or array or collection (or anything else you want). Once you have completed this task you can reference *columns* and *rows* in this object. Here is ONE example of how you could do this: http://stackoverflow.com/questions/34275183/write-large-collection-object-parsed-from-json-to-excel-range – Ralph Dec 01 '16 at 14:07
  • @Ralph Thank you Ralph. I'll now proceed to modify my code. If you can post your comment as an answer, I'll accept it. – Mohideen Imran Khan Dec 02 '16 at 13:20

0 Answers0