Hiii. After running this coding, I still did not get authorization from Jira . Any solution to fix this problem?
In addition, how to exceed the maxResult 50 by displaying the total issues into excel sheet?
Sub Extract_Jira_Data()
Call testAuthen
End Sub
Public Function UserPassBase64() As String
Dim objXML As Variant
Dim objNode As MSXML2.IXMLDOMElement
Dim arrData() As Byte
Dim UserNameP As String
UserNameP = "userName:password"
arrData = StrConv(UserNameP, vbFromUnicode)
Set objXML = CreateObject("MSXML2.DOMDocument")
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
UserPassBase64 = objNode.text
End Function
Public Sub testAuthen()
Set ws = ThisWorkbook
ws.Sheets("Sheet2").Activate
Dim userName, password As String
userName = ActiveSheet.OLEObjects("txtUserNameIn").Object.text
password = ActiveSheet.OLEObjects("txtPasswordIn").Object.text
Dim JiraService As New MSXML2.XMLHTTP60
UserNameP = UserPassBase64
With JiraService
.Open "GET", query
.setRequestHeader "Content-Type", "application/json"
.setRequestHeader "Accept", "application/json"
.setRequestHeader "Authorization", "Basic " & UserNameP
.send ""
MsgBox .Status
If .Status = "401" Then
MsgBox "Not authorized or invalid username/password"
Else
MsgBox "Correct credential!!"
End If
Set ws = ThisWorkbook
ws.Sheets("JiraExcel").Activate
Range("A2:AZ65536").Clear
Dim Json As Object
Dim j As Integer
'Dim i As Integer
Set Json = JsonConverter.ParseJson(.responseText)
j = 2
For j = j To 50
'C3 Num
ActiveSheet.Cells(j, 1) = Json("issues")(j)("fields")("customfield_10400")
'Issue ID
ActiveSheet.Cells(j, 2) = Json("issues")(j)("key")
Next j
End With
End Sub
