API not passing Basic Auth credentials correctly

yits05

New Member
Joined
Jul 17, 2020
Messages
47
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I am a bit out of my depth here, and was hoping for some guidance on what I am doing wrong. I am trying to make the below API call through VBA, passing my API key as base64 encoded as required. However, the response has constantly been "invalid auth credentials". The API Key has been confirmed as working by the vendor.

Here is what the API documentation requires:
Rich (BB code):
curl -X PUT 'https://harvest.greenhouse.io/v1/jobs/{id}'
-H "Content-Type: application/json"
-H "On-Behalf-Of: {greenhouse user ID}"
-H "Authorization: Basic abcdefg123"

It further states that the auth header needs to be structured as Authorization: Basic <base64("username:password")>

Since only a username needs to be provided in this call, I also need to append a : (colon) to to Greenhouse API token and then Base64 encode the resulting string.
Thank you!

Here is my code:

VBA Code:
Function EncodeBase64(text As String) As String
  Dim arrData() As Byte
  arrData = StrConv(text, vbFromUnicode)

  Dim objXML As MSXML2.DOMDocument
  Dim objNode As MSXML2.IXMLDOMElement

  Set objXML = New MSXML2.DOMDocument
  Set objNode = objXML.createElement("b64")

  objNode.DataType = "bin.base64"
  objNode.nodeTypedValue = arrData
  EncodeBase64 = objNode.text

  Set objNode = Nothing
  Set objXML = Nothing
End Function
Public Sub ChangeCoordinators()

    On Error Resume Next
    Dim jobid As Variant
    Dim Strresponse As Variant
    Dim i As Long
    filepath = Sheets("Sheet2").Range("A" & i)
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Dim Sheet As Worksheet
For i = 1 To FinalRow
    Set Sheet = Sheets("Sheet2")

    Const APIkey = "sampleAPIkey123" & ":"

    
    Dim httpReq As Object
    Set httpReq = CreateObject("MSXML2.XMLHTTP")

    Dim rootURL As String, registrationEndpointURL As String
    Dim registration As String
    Dim text As String
    Dim userid As Variant
    
    rootURL = "https://harvest.greenhouse.io/v1/jobs/" & filepath
    
    
    With httpReq
        .Open "PUT", rootURL, False
        xmlhttp.setRequestHeader "Content-Type: ", "application/json"
        xmlhttp.setRequestHeader "On-Behalf-Of: ", "678910"
        xmlhttp.setRequestHeader "Authorization: ", "Basic ", EncodeBase64(APIkey)
        .send ("{" & """coordinators""" & ":" & " [{" & """user_id""" & ":" & "12345" & "," & """responsible_for_future_work""" & ":" & " true" & "," & """responsible_for_active_work""" & ":" & "true" & "," & """responsible_for_inactive_work""" & ":" & "false" & "}" & "]" & "}")
        Strresponse = .responseText
        Worksheets("Users").Range("A1").Value = Strresponse
        
        
    End With

Next i

    
End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
Remove the colons and append the encoded API key.
VBA Code:
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "On-Behalf-Of", "678910"
        .setRequestHeader "Authorization", "Basic " & EncodeBase64(APIkey)
 

yits05

New Member
Joined
Jul 17, 2020
Messages
47
Office Version
  1. 2016
Platform
  1. Windows
Remove the colons and append the encoded API key.
VBA Code:
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "On-Behalf-Of", "678910"
        .setRequestHeader "Authorization", "Basic " & EncodeBase64(APIkey)
Thanks - I tried that but still the same response
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,751
Are you still using the xmlhttp variable in those lines? You shouldn't because it's undefined and undeclared. Those lines should be exactly as I posted, inside the With httpReq ... End With block.

What is the On Error Resume Next for? Remove it because it only hides any errors. For example this line will produce an error because i is zero (the cell A0 doesn't exist):
VBA Code:
filepath = Sheets("Sheet2").Range("A" & i)
and therefore filepath is empty. Correction - assign the correct row number to i.

Add Option Explicit at the top of the module and declare all variables.
 
Solution

yits05

New Member
Joined
Jul 17, 2020
Messages
47
Office Version
  1. 2016
Platform
  1. Windows
Are you still using the xmlhttp variable in those lines? You shouldn't because it's undefined and undeclared. Those lines should be exactly as I posted, inside the With httpReq ... End With block.

What is the On Error Resume Next for? Remove it because it only hides any errors. For example this line will produce an error because i is zero (the cell A0 doesn't exist):
VBA Code:
filepath = Sheets("Sheet2").Range("A" & i)
and therefore filepath is empty. Correction - assign the correct row number to i.

Add Option Explicit at the top of the module and declare all variables.
This worked, thank you so much.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,573
Messages
5,637,155
Members
416,959
Latest member
Mohzein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top