Using matrix api in excel vba

[uhrig....@gmail.com]

Hello,

I'm trying to use the ors Matrix API with VBA (Excel 2016) with the following expresion (similar to the example in the documentation):

---------------------------------

Option Explicit
 
Sub XMLHttpTest()
    Dim XMLHttp As Object
    Dim strURL As String, strMethod As String, strUser As String
    Dim strPassword As String
    Dim bolAsync As Boolean
    Dim varMessage
    Dim WS As Excel.Worksheet
    Set WS = ActiveSheet
    Set XMLHttp = CreateObject("MSXML2.XMLHTTP")

    strURL = "https://api.openrouteservice.org/matrix?api_key=your-api-key&profile=driving-car&locations=9.970093%2C48.477473%7C9.207916%2C49.153868%7C37.573242%2C55.801281%7C115.663757%2C38.106467&sources=all&destinations=all&metrics=duration&resolve_locations=&units=m&optimized=true"
    Call XMLHttp.Open("GET", strURL, False)
    Call XMLHttp.setRequestHeader("Accept", "application/json; charset=utf-8")
    Call XMLHttp.send
    WS.Cells(2, 1).Value = XMLHttp.Status
    WS.Cells(2, 2).Value = XMLHttp.responseText
    WS.Cells(2, 3).Value = XMLHttp.getAllResponseHeaders 
End Sub

--------------------------------

This approach fails and I'm receiving the following output

Status: 403

Response Text: "error": "Key not authorised"

Response Header:

Server: nginx

Date: Wed, 30 May 2018 06:08:54 GMT

Content-Type: application/json

Content-Length: 37

Connection: keep-alive

X-Generator: tyk.io

I guess, there is a problem with the Header. Does anybody know the problem?
 Thank you in advance!

Best regards,

Martin

[Timothy]

Hi Martin, does the request work using cURL? Cheers, Tim

[Martin]

Hi Tim,

thank you for your fast Response.

according to the documentation, the request for cURL looks different:

curl --include \     --header "Accept: application/json; charset=utf-8" \  'https://api.openrouteservice.org/matrix?api_key=your-api-key&profile=driving-car&locations=9.970093%2C48.477473%7C9.207916%2C49.153868%7C37.573242%2C55.801281%7C115.663757%2C38.106467&sources=all&destinations=all&metrics=duration&resolve_locations=&units=m&optimized=true'

So I think, it doesn't work. Is it this, what you mean?

Martin

[Martin]

Hi again,

when I'm using the right key it works...my mistake... 

Here is my solution, that works:

-------------------------------

Option Explicit
 
Sub XMLHttpTest()
    Dim XMLHttp As Object

    Dim strURL As String, strResponse As String
    Set XMLHttp = CreateObject("WinHttp.WinHttpRequest.5.1")

    strURL = "https://api.openrouteservice.org/matrix?api_key=MYKEY&profile=driving-car&locations=9.970093%2C48.477473%7C9.207916%2C49.153868%7C37.573242%2C55.801281%7C115.663757%2C38.106467&sources=all&destinations=all&metrics=duration&resolve_locations=&units=m&optimized=true"
   With XMLHttp
        .Open "GET", strURL, False
        .setRequestHeader "Accept", "application/json; charset=utf-8"
        .send
        .WaitForResponse
        strResponse = .responseText
        Debug.Print strResponse
        Debug.Print .Status
        Debug.Print .getAllResponseHeaders
    End With

    Dim WS As Excel.Worksheet
    Set WS = ActiveSheet

    WS.Cells(2, 1).Value = XMLHttp.Status
    WS.Cells(2, 2).Value = XMLHttp.responseText
    WS.Cells(2, 3).Value = XMLHttp.getAllResponseHeaders
End Sub

-------------------------------