如何在VBA中传递身份验证凭据

Question

我正在尝试编写一个VBA宏，以传递我的凭据到一个地址并获取一些内容（JIRA的REST API），但是我在将我的代码从Java转换为VBA时遇到了一些困难。目前，这是我的Java代码：

        String username = "myUser";
        String password = "myPassword";

        String authString = username + ":" + password;
        byte[] authEncBytes = Base64.encodeBase64(authString.getBytes());
        String authStringEnc = new String(authEncBytes);
        System.out.println("Base64 encoded auth string: " + authStringEnc);

        URL url = new URL(address);
        URLConnection urlConnection = url.openConnection();
        urlConnection.setRequestProperty("Authorization", "Basic "
                + authStringEnc);
        InputStream is = urlConnection.getInputStream();
        InputStreamReader isr = new InputStreamReader(is);

我正在尝试将这个转换为VBA语言，但我并不完全确定如何处理它，或者是否有一些库可以方便地实现这个。

- Stupid.Fat.Cat

Answer 1

对于基本身份验证，您可以简单地执行以下操作：

Dim response As String

With CreateObject("Microsoft.XMLHTTP")
  .Open "GET", address, false, username, password
  .Send
  response = .responseText
End With

Msgbox response

- Alex K.

---

我已经使用HTTPS进行了测试，它完美地工作。这是最简单的解决方案。 - Unicco

Answer 2

Sub test()
 Dim user As String
 Dim pwd As String
 Dim path As String
 user = ""
 pwd = ""
 path = ""
 Debug.Print httpGET(path, user, pwd)
End Sub

Public Function httpGET(fn As String, _
        Optional authUser As String = vbNullString, _
        Optional authPass As String = vbNullString) As String
    pHtml = fn
    Dim oHttp As Object
    Set oHttp = CreateObject("Microsoft.XMLHTTP")
    Call oHttp.Open("GET", pHtml, False)
    If (authUser <> vbNullString) Then
    ' need to do basic authentication
    ' acknowledgement to http://pastie.org/1192157
        oHttp.SetRequestHeader "Content-Type", "application/json"
        oHttp.SetRequestHeader "Accept", "application/json"
        oHttp.SetRequestHeader "Authorization", "Basic " + _
            EncodeBase64(authUser + ":" + authPass)
    End If
    Call oHttp.Send("")
    httpGET = oHttp.ResponseText
    Set oHttp = Nothing
End Function


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 = Application.Clean(objNode.text)

  Set objNode = Nothing
  Set objXML = Nothing
End Function

- Bhanu Sinha

Answer 3

使用“Authorization”请求头。 “Authorization”请求头需要加密的字符串作为用户名和密码。

.setRequestHeader "Authorization", "Basic " & EncodeBase64

因此，在这里，JiraService 是一个 XMLHTTP 对象，类似于以下方式进行身份验证，其中 EncodeBase64 是一个返回加密字符串的函数。

Public Function isAuthorized() As Boolean
With JiraService
    .Open "POST", sURL & "/rest/api/2/issue/", False
    .setRequestHeader "Content-Type", "application/json"
    .setRequestHeader "Accept", "application/json"
    .setRequestHeader "Authorization", "Basic " & EncodeBase64
    .send ""
    If .Status <> 401 Then
        isAuthorized = True
    Else
        isAuthorized = False
    End If
End With

Set JiraService = Nothing
End Function

您可以在此处查看完整的VBA示例

- cyboashu