Java 如何在 VBA 中传递身份验证凭据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22149169/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pass authentication credentials in VBA
提问by Stupid.Fat.Cat
I'm trying to write a VBA macro that would pass my credentails to an address and fetch some content (REST API for JIRA), but I'm having some difficulties converting my code from java to VBA. Currently, this is my java code:
我正在尝试编写一个 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);
And I'm trying to convert this to VBA, I'm not entirely sure how to handle it, or if there's some library that would facilitate this.
我正在尝试将其转换为 VBA，我不完全确定如何处理它，或者是否有一些库可以促进这一点。
回答by Alex K.
For Basic Authentication you can simply:
对于基本身份验证，您可以简单地：
Dim response As String
With CreateObject("Microsoft.XMLHTTP")
.Open "GET", address, false, username, password
.Send
response = .responseText
End With
Msgbox response
回答by cyboashu
Use the "Authorization" request header. The “Authorization” request header requires an encrypted string for username and password.
使用“授权”请求标头。“授权”请求标头需要一个加密的用户名和密码字符串。
.setRequestHeader "Authorization", "Basic " & EncodeBase64
So here JiraServicebeing an XMLHTTP object, something like this will authenticate, where EncodeBase64is a function which returns encrypted string.
所以这里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
You can check out a complete VBA example here
您可以在此处查看完整的 VBA 示例
回答by Bhanu Sinha
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
