Anytime someone says they have a great macro, don’t trust them. That said; I have a great macro that creates an Excel function that will retrieve system coordinates from EDSM and place them in your spreadsheet.
For example, you want the Z coordinate for the Gateway system. Type:
=GetEliteDangerSysCoord(“Gateway”,”z”)
in the cell, and the function puts in “ -0.875”
Instead of typing the system name and coordinate, you can use the cell reference like this:
You may get the error below since EDSM is a secure site. Click “yes,” and the function will work for the rest of the time you have Excel open.
Here is the code you can cut and paste into a module using Excel’s Visual Basic editor. Look at it first to make sure it’s safe.
For example, you want the Z coordinate for the Gateway system. Type:
=GetEliteDangerSysCoord(“Gateway”,”z”)
in the cell, and the function puts in “ -0.875”
Instead of typing the system name and coordinate, you can use the cell reference like this:
You may get the error below since EDSM is a secure site. Click “yes,” and the function will work for the rest of the time you have Excel open.
Here is the code you can cut and paste into a module using Excel’s Visual Basic editor. Look at it first to make sure it’s safe.
Option Explicit
Public Function GetEliteDangerSysCoord(ByRef SystemName As String, ByRef XYorZCoord As String) As Double
' EDSM (Elite Dangerous Star Map) is a
'community effort to store and calculate systems coordinates around the Elite: Dangerous Galaxy.
'http://www.edsm.net/
'Pass the System Name -> "SysteName" and which coordinate you want X, Y, or Z -> "XYorZCoord"
Dim EDSMresource As String 'Base URL with appended with system name to request coordinates
Dim EDSMresponse As Object 'JSON data returned from EDSM for example {"name":"LHS 3447","coords":{"x":-43.1875,"y":-5.28125,"z":56.15625}}
'(JavaScript Object Notation)
Dim EDSMjsonData As String 'Store as an ordinary string the JSON data to parse
Dim intStartJson As Integer 'Number of charactures from left the coord starts in the JSON data, for LHS 3447 the x-coord starts 34 charactures from the left
Dim intCoordLenJson As Integer 'Number of charactures in the JSON data for the coordinate, for LHS 3447 the x-coord is -43.1875 or 8 charactures
' The system name must be in URL format. For example, the system BD+66 696 has a plus sign; the plus sign must be converted to %2B,
' and the space must be converted to +
' Therefore, the system BD%2B66+696 gives the correct cordinates
SystemName = URLEncode(SystemName) ' the below macro encodes into URL format
' URL of EDSM to return the JSON data of system coordinates
EDSMresource = "https://www.edsm.net/api-v1/system?sysname=" & SystemName & "&coords=1"
On Error Resume Next
'Get the JSON data
Set EDSMresponse = CreateObject("Microsoft.XMLHTTP")
With EDSMresponse
.Open "GET", EDSMresource, False
.send
EDSMjsonData = .responseText
End With
'Parse the JSON data to return the X, Y, or Z Coordinate
Select Case XYorZCoord
Case "x", "X"
'Trim off the first part of the JSON data - > {"name":"LHS 3447","coords":{"x":
'InStr returns 29, which is the number of charactures up to and including the : before the {"x":
'InStrRev returns 42, which is the number of charactures up to and including the , in ,"y":
intStartJson = InStr(EDSMjsonData, "{" & Chr(34) & "x" & Chr(34) & ":") + 5 ' add five for the {"x": note Chr(34) is the double quote
intCoordLenJson = InStrRev(EDSMjsonData, "," & Chr(34) & "y" & Chr(34) & ":") - intStartJson
Case "y", "Y"
intStartJson = InStr(EDSMjsonData, "," & Chr(34) & "y" & Chr(34) & ":") + 5
intCoordLenJson = InStrRev(EDSMjsonData, "," & Chr(34) & "z" & Chr(34) & ":") - intStartJson
Case "z", "Z"
intStartJson = InStr(EDSMjsonData, "," & Chr(34) & "z" & Chr(34) & ":") + 5
intCoordLenJson = InStrRev(EDSMjsonData, "}}") - intStartJson
Case Else
'If is not a lower or upper case x,y,or z
'return 9999 and then exit function
GetEliteDangerSysCoord = 9999
Set EDSMresponse = Nothing
Exit Function
End Select
'The Mid returns the coordinate parsed from the JSON data
GetEliteDangerSysCoord = Mid(EDSMjsonData, intStartJson, intCoordLenJson)
Set EDSMresponse = Nothing
End Function
Function URLEncode(ByVal Text As String) As String
'This function takes the string and converts to URL format
' I found this macro at http://sevenwires.blogspot.com/2009/07/code-encode-url-in-excel.html
' Thanks to those who wrote it
Dim i As Integer
Dim acode As Integer
Dim char As String
URLEncode = Text
For i = Len(URLEncode) To 1 Step -1
acode = Asc(Mid$(URLEncode, i, 1))
Select Case acode
Case 48 To 57, 65 To 90, 97 To 122
' don't touch alphanumeric chars
Case 32
' replace space with "+"
Mid$(URLEncode, i, 1) = "+"
Case Else
' replace punctuation chars with "%hex"
URLEncode = Left$(URLEncode, i - 1) & "%" & Hex$(acode) & Mid$(URLEncode, i + 1)
End Select
Next
End Function
Public Function GetEliteDangerSysCoord(ByRef SystemName As String, ByRef XYorZCoord As String) As Double
' EDSM (Elite Dangerous Star Map) is a
'community effort to store and calculate systems coordinates around the Elite: Dangerous Galaxy.
'http://www.edsm.net/
'Pass the System Name -> "SysteName" and which coordinate you want X, Y, or Z -> "XYorZCoord"
Dim EDSMresource As String 'Base URL with appended with system name to request coordinates
Dim EDSMresponse As Object 'JSON data returned from EDSM for example {"name":"LHS 3447","coords":{"x":-43.1875,"y":-5.28125,"z":56.15625}}
'(JavaScript Object Notation)
Dim EDSMjsonData As String 'Store as an ordinary string the JSON data to parse
Dim intStartJson As Integer 'Number of charactures from left the coord starts in the JSON data, for LHS 3447 the x-coord starts 34 charactures from the left
Dim intCoordLenJson As Integer 'Number of charactures in the JSON data for the coordinate, for LHS 3447 the x-coord is -43.1875 or 8 charactures
' The system name must be in URL format. For example, the system BD+66 696 has a plus sign; the plus sign must be converted to %2B,
' and the space must be converted to +
' Therefore, the system BD%2B66+696 gives the correct cordinates
SystemName = URLEncode(SystemName) ' the below macro encodes into URL format
' URL of EDSM to return the JSON data of system coordinates
EDSMresource = "https://www.edsm.net/api-v1/system?sysname=" & SystemName & "&coords=1"
On Error Resume Next
'Get the JSON data
Set EDSMresponse = CreateObject("Microsoft.XMLHTTP")
With EDSMresponse
.Open "GET", EDSMresource, False
.send
EDSMjsonData = .responseText
End With
'Parse the JSON data to return the X, Y, or Z Coordinate
Select Case XYorZCoord
Case "x", "X"
'Trim off the first part of the JSON data - > {"name":"LHS 3447","coords":{"x":
'InStr returns 29, which is the number of charactures up to and including the : before the {"x":
'InStrRev returns 42, which is the number of charactures up to and including the , in ,"y":
intStartJson = InStr(EDSMjsonData, "{" & Chr(34) & "x" & Chr(34) & ":") + 5 ' add five for the {"x": note Chr(34) is the double quote
intCoordLenJson = InStrRev(EDSMjsonData, "," & Chr(34) & "y" & Chr(34) & ":") - intStartJson
Case "y", "Y"
intStartJson = InStr(EDSMjsonData, "," & Chr(34) & "y" & Chr(34) & ":") + 5
intCoordLenJson = InStrRev(EDSMjsonData, "," & Chr(34) & "z" & Chr(34) & ":") - intStartJson
Case "z", "Z"
intStartJson = InStr(EDSMjsonData, "," & Chr(34) & "z" & Chr(34) & ":") + 5
intCoordLenJson = InStrRev(EDSMjsonData, "}}") - intStartJson
Case Else
'If is not a lower or upper case x,y,or z
'return 9999 and then exit function
GetEliteDangerSysCoord = 9999
Set EDSMresponse = Nothing
Exit Function
End Select
'The Mid returns the coordinate parsed from the JSON data
GetEliteDangerSysCoord = Mid(EDSMjsonData, intStartJson, intCoordLenJson)
Set EDSMresponse = Nothing
End Function
Function URLEncode(ByVal Text As String) As String
'This function takes the string and converts to URL format
' I found this macro at http://sevenwires.blogspot.com/2009/07/code-encode-url-in-excel.html
' Thanks to those who wrote it
Dim i As Integer
Dim acode As Integer
Dim char As String
URLEncode = Text
For i = Len(URLEncode) To 1 Step -1
acode = Asc(Mid$(URLEncode, i, 1))
Select Case acode
Case 48 To 57, 65 To 90, 97 To 122
' don't touch alphanumeric chars
Case 32
' replace space with "+"
Mid$(URLEncode, i, 1) = "+"
Case Else
' replace punctuation chars with "%hex"
URLEncode = Left$(URLEncode, i - 1) & "%" & Hex$(acode) & Mid$(URLEncode, i + 1)
End Select
Next
End Function