PDA

View Full Version : How to create a GeoJSON editor with VBA



waimea
03-10-2019, 09:33 AM
Hi,

I am trying to create an userform that outputs a file in GeoJSON.

GeoJSON is a https://en.wikipedia.org/wiki/GeoJSON fileformat for geospatial information.

I know how to create the userform and I would like to add 16 properties where some of them can be blank.



{
"type": "FeatureCollection",
"features": [
{
"type": "Feature",
"properties": {
"Propertie1": "Value1",
"Propertie2": "Value2",
"Propertie3": "Value3",
"Propertie4": "Value4",
"Propertie5": "Value5",
"Propertie6": "Value6",
"Propertie7": "Value7",
"Propertie8": "Value8",
"Propertie9": "Value9",
"Propertie10": "Value10",
"Propertie11": "Value11",
"Propertie12": "Value12",
"Propertie13": "Value13",
"Propertie14": "Value14",
"Propertie15": "Value15",
"Propertie16": "Value16",
},
"geometry": {
"type": "Point",
"coordinates": [
15.1561231232
60.54545678424146
]
}
},
{
"type": "Feature",
"properties": {
"Propertie1": "Value1",
"Propertie2": "Value2",
"Propertie3": "Value3",
"Propertie4": "Value4",
"Propertie5": "Value5",
"Propertie6": "Value6",
"Propertie7": "Value7",
"Propertie8": "Value8",
"Propertie9": "Value9",
"Propertie10": "Value10",
"Propertie11": "Value11",
"Propertie12": "Value12",
"Propertie13": "Value13",
"Propertie14": "Value14",
"Propertie15": "Value15",
"Propertie16": "Value16",
},
"geometry": {
"type": "Point",
"coordinates": [
20.91595458984375,
60.47159370768037
]
}
},






{
"type": "FeatureCollection",
"features": [
{
"type": "Feature",


The first entry has a different structure than the other entries, see the feature collection. The last entry looks like this at the bottom.



]
}
}
]
}


I have an userform with 18 labels and 18 textboxes. 16 textboxes for the properties and 2 textboxes for latitude and longitude.

I might change one or more of the textboxes to combo boxes.

How do I create the GeoJSON structure with VBA? Since the beginning and the end are different I don't know where to start?

waimea
03-11-2019, 02:06 AM
I have googled a lot and I found this page http://excelerator.solutions/2017/08/24/convert-excel-to-json-using-vba/ with the following code:



Dim excelRange As Range
Dim jsonItems As New Collection
Dim jsonDictionary As New Dictionary
Dim i As Long
Dim cell As Variant

Set excelRange = Cells(1, 1).CurrentRegion

For i = 2 To excelRange.Rows.Count
jsonDictionary("id") = Cells(i, 1)
jsonDictionary("name") = Cells(i, 2)
jsonDictionary("username") = Cells(i, 3)
jsonDictionary("email") = Cells(i, 4)
jsonDictionary("street") = Cells(i, 5)
jsonDictionary("suite") = Cells(i, 6)
jsonDictionary("city") = Cells(i, 7)
jsonDictionary("zipcode") = Cells(i, 8)
jsonDictionary("phone") = Cells(i, 9)
jsonDictionary("website") = Cells(i, 10)
jsonDictionary("company") = Cells(i, 11)

jsonItems.Add jsonDictionary
Set jsonDictionary = Nothing
Next i

MsgBox JsonConverter.ConvertToJson(jsonItems, Whitespace:=3)



This code creates a JSON file, I want to create a GeoJSON file.

How can I adapt this so that the first entry and the last entries are different?

waimea
03-12-2019, 01:07 AM
Any suggestions on where to start? I need a hint?

snb
03-14-2019, 01:03 AM
I have an userform with 18 labels and 18 textboxes. 16 textboxes for the properties and 2 textboxes for latitude and longitude.


So why don't you post it here ?