API not passing Basic Auth credentials correctly

yits05

Board Regular
Joined
Jul 17, 2020
Messages
55
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,769
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

Board Regular
Joined
Jul 17, 2020
Messages
55
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,769
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

Board Regular
Joined
Jul 17, 2020
Messages
55
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,130,433
Messages
5,642,088
Members
417,256
Latest member
JessAw

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