Solved Connect Access with Woocommerce via REST API (1 Viewer)

[gemma-the-husky]

I sent you a PM
I need a site other than example.com - ie your actual website name

 

[cheekybuddha]

but I don't have a Bearer token.

Bearer token will come in a cookie in the http response. You'll likely not have one before you have authenticated

 

[gemma-the-husky]

It's good now. I sent you working code in a PM.

 

[theDBguy]

It's good now. I sent you working code in a PM.

Just curious, did you use Bearer or Basic?

 

[gemma-the-husky]

I used the VBA I posted in #6, but I needed a real website - the WooCommerce help see #13 used www.example.com, but that returned a 400 error with a text (json) response I didn't really understand. @perlfan gave me his website, and when I replaced example.com with his website it returned the metadata (if you will), but to return genuine data such as orders, you need to pass in authorisation details, which I didn't have. The WooCommerce RestAPI site gives examples of how to construct the request.

I think it only returns data with e-commerce sites that do use WooCommerce, so you can't easily test it without a genuine site.

I wonder if when it says curl blah on the WooCommerce , the curl command is a c variant of the URL. Anyway it was pretty tricky to find the right way of doing it with VBA, but there's snippets here and there.

I think the authorisation is passed in with the action request, rather than as a separate authorization as in my example, or maybe there's a choice of ways to do it.

 

[theDBguy]

I used the VBA I posted in #6, but I needed a real website - the WooCommerce help see #13 used www.example.com, but that returned a 400 error with a text (json) response I didn't really understand. @perlfan gave me his website, and when I replaced example.com with his website it returned the metadata (if you will), but to return genuine data such as orders, you need to pass in authorisation details, which I didn't have. The WooCommerce RestAPI site gives examples of how to construct the request.

I think it only returns data with e-commerce sites that do use WooCommerce, so you can't easily test it without a genuine site.

I wonder if when it says curl blah on the WooCommerce , the curl command is a c variant of the URL. Anyway it was pretty tricky to find the right way of doing it with VBA, but there's snippets here and there.

I think the authorisation is passed in with the action request, rather than as a separate authorization as in my example, or maybe there's a choice of ways to do it.

Okay, thanks. I was just curious. The code you posted in #6 used Bearer authentication, but I saw in their API documentation that they only need Basic authentication.

 

[gemma-the-husky]

This site

https://woocommerce.github.io/woocommerce-rest-api-docs/#index

Said checking the account info (only) didn't need any authorisation, but other commands needed credentials to be included.

My own experience was with bearer authentication, but the WooCommerce site shows the authentication being passed in differently. Whether that is basic, or just another way of providing bearer information, I don't know to be honest, and I didn't expect the op to give me his actual login credentials anyway.

 

[gemma-the-husky]

Okay, thanks. I was just curious. The code you posted in #6 used Bearer authentication, but I saw in their API documentation that they only need Basic authentication.

Bit of a pain to do in the end. @perlfan created a staging website for me and set me up with a login, that produced a userkey and userpassword. This link

Authentication over HTTPS – WooCommerce REST API Documentation - WP REST API v3

The documentation says the authentication is managed within the URL you use with the GET or POST, and explains what you should do, but I couldn't get the -u syntax to work. I tried loads of things.

I was finally able to get it working with the alternative ? syntax

curl https://www.example.com/wp-json/wc/v3/orders?consumer_key=123&consumer_secret=abc

I think the key was to have no spaces at all in the text after the ?

so this code below, with the WOO constants provided by @perlfan and with no Bearer or Basic command produces a 200 response, and a JSON .responsetext. The authentication/authorisation appears to be managed by the key and secret pair supplied by the WooCommerce/Wordpress login, and here stored as constants WOOkey and WOOsecret.

I should add finally, @perlfan is actually in Germany, and all the responses including 401 and 404 errors were in German which didn't help either.

Const WOOurl = "https://example.com/wp-json/wc/v3"
'(with example.com replaced by a real WooCommerce website)

strUrl = WOOurl & "/orders"
strUrl = strUrl & "?consumer_key=" & WOOkey & "&consumer_secret=" & WOOsecret

    With objRequest
        .Open "GET", strUrl, blnAsync
     
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "Cache-Control", "max-age-0"
        .send

 

[theDBguy]

Bit of a pain to do in the end. @perlfan created a staging website for me and set me up with a login, that produced a userkey and userpassword. This link

Authentication over HTTPS – WooCommerce REST API Documentation - WP REST API v3

The documentation says the authentication is managed within the URL you use with the GET or POST, and explains what you should do, but I couldn't get the -u syntax to work. I tried loads of things.

I was finally able to get it working with the alternative ? syntax

curl https://www.example.com/wp-json/wc/v3/orders?consumer_key=123&consumer_secret=abc

I think the key was to have no spaces at all in the text after the ?

so this code below, with the WOO constants provided by @perlfan and with no Bearer or Basic command produces a 200 response, and a JSON .responsetext. The authentication/authorisation appears to be managed by the key and secret pair supplied by the WooCommerce/Wordpress login, and here stored as constants WOOkey and WOOsecret.

I should add finally, @perlfan is actually in Germany, and all the responses including 401 and 404 errors were in German which didn't help either.

Const WOOurl = "https://example.com/wp-json/wc/v3"
'(with example.com replaced by a real WooCommerce website)

strUrl = WOOurl & "/orders"
strUrl = strUrl & "?consumer_key=" & WOOkey & "&consumer_secret=" & WOOsecret

    With objRequest
        .Open "GET", strUrl, blnAsync
   
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "Cache-Control", "max-age-0"
        .send

Hi Dave. Thanks for the update. Glad to hear you got it sorted out. Cheers!

PS. That was exactly the page I saw, and it says it right at the top. That's why I got confused.

 

[gemma-the-husky]

Hi Dave. Thanks for the update. Glad to hear you got it sorted out. Cheers!

PS. That was exactly the page I saw, and it says it right at the top. That's why I got confused.
View attachment 103065

I tried to do that but I wasn't clear on the right syntax to include the necessary details. It's so hard to find VBA code samples.

.setRequestHeader "Basic" etc

 

[theDBguy]

It's so hard to find VBA code samples.

I definitely agree with that.

 

[perlfan]

Fantastic effort, Dave!! You didn't give up and finally worked out a code that works perfectly! Finally it's documented how to authenticate with Woocommerce from MS Access & VBA and retrieve information via REST API. Thank you!!

 

[perlfan]

And this is the code to authenticate with Woocommerce from MS Access & VBA and retrieve information via REST API:

Option Compare Database
Option Explicit

Const WOOkey = "ck_XYZ"
Const WOOsecret = "cs_XYZ"
Const WOOtoken = "ck_XYZ:cs_XYZ"
Const WOOurl = "https://yoururl.com/wp-json/wc/v3"


Private Sub GetWooAccountInfo()

    Dim JSONObject As Object
    Dim objRequest As Object
    Dim strUrl As String
    Dim blnAsync As Boolean
    Dim strResponse As String

    Dim s As String
    Dim fname As String
    Dim fno As Long

    DoEvents
    Set objRequest = Nothing
    Set objRequest = CreateObject("MSXML2.XMLHTTP")

    strUrl = WOOurl

    blnAsync = False

    With objRequest
        .Open "GET", strUrl, blnAsync
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "Cache-Control", "max-age-0"
            
        .send
    
        'wait whilst waiting for response
        While objRequest.ReadyState <> 4
            DoEvents
        Wend
    
        If .Status <> 200 Then
            MsgBox "Sorry. There was an error retrieving the orders: Status: " & .Status
            strResponse = .responsetext
        Else
            MsgBox "Data retrieved Successfully: Status " & .Status
            strResponse = .responsetext
        End If
    
        Set objRequest = Nothing
    End With
        
    fno = FreeFile
    fname = CurrentProject.Path & "\" & "JSONText-" & Format(Now, "yyyy-mm-dd-hhnnss") & ".txt"

    Open fname For Output As fno

    Print #fno, strResponse
    Close #fno

    Application.FollowHyperlink fname

    'Set JSONObject = JsonConverter.ParseJson(strResponse)
    'i got my parseJson code from GitHub

    Exit Sub

End Sub


Private Sub GetWooOrders()

    Dim JSONObject As Object
    Dim objRequest As Object
    Dim strUrl As String
    Dim blnAsync As Boolean
    Dim strResponse As String

    Dim s As String
    Dim fname As String
    Dim fno As Long

    DoEvents
    Set objRequest = Nothing
    Set objRequest = CreateObject("MSXML2.XMLHTTP")
    
    strUrl = WOOurl & "/orders"

    'this was given as an alternative to the -u solution
    strUrl = strUrl & "?consumer_key=" & WOOkey & "&consumer_secret=" & WOOsecret

    'MsgBox strUrl

    blnAsync = False

    With objRequest
        .Open "GET", strUrl, blnAsync
    
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "Cache-Control", "max-age-0"
        .send
    
        'wait whilst waiting for response
        While objRequest.ReadyState <> 4
            DoEvents
        Wend
    
        If .Status <> 200 Then
            MsgBox "Sorry. There was an error retrieving the account information: Status: " & .Status
            strResponse = .responsetext
        Else
            MsgBox "Data retrieved Successfully: Status " & .Status
            strResponse = .responsetext
        End If
    
        Set objRequest = Nothing
    End With
        
    fno = FreeFile
    fname = CurrentProject.Path & "\" & "JSONText-" & Format(Now, "yyyy-mm-dd-hhnnss") & ".txt"

    Open fname For Output As fno

    Print #fno, strResponse
    Close #fno

    Application.FollowHyperlink fname


    Exit Sub

End Sub