MSXML2.XMLHTTP EXREL VBA GET-AZURE-TOKEN代码错误W8 64位华硕N56VZ带办公用品2013 32BIT用于MICROSOFT TRANSLATOR SERVICE [英] MSXML2.XMLHTTP ERROR IN EXCEL VBA GET-AZURE-TOKEN CODE ON W8 64BIT ASUS N56VZ WITH OFFICE 2013 32BIT FOR MICROSOFT TRANSLATOR SERVICE
问题描述
在XP / Excel 2003，Vista / Excel 2007，W7 / Excel 2007，XP / Excel 2010（所有32位系统和应用程序）上正常工作时，获取限时翻译器访问代码的代码确实如此不适用于Office 32bit的64位Windows 8系统。
原始代码的主要功能:(基于Patrick O'Beirne的解决方案（sysmod dot com）
函数GetAccessToken（）As String
'检查AccessToken的有效性，如果时间已过，则请求新的
'事先你必须得到你的"客户ID"和"客户秘密"来自
'https：//datamarket.azure.com/developer/applications
'Webrequest definitons
Dim sRequest As String
Dim webRequest As MSXML2.XMLHTTP
Dim TrlClientID As String，TrlAppName As String，TrlClientSecret As String
Dim TrlRedirectURI As String，TrlAppDescr As String，TrlURICheckOAUTH As String
'Webrequest response text definitions
Dim RTArr As Variant，RTTokenType As String，RTAccessToken As String，RTExpiresIn As String，RTScope As String
Static sAccess_Token As String，dtExpiry_Time As Date
'Get"quot; old"来自工作表配置的Expire-Time和AccessToken
dtExpiry_Time =范围（"TrlExpirationTime"）。值
sAccess_Token =范围（" TrlAccessToken"）。值
'如果AccessToken超时获得新的
如果Now（）> dtExpiry_Time然后
TrlClientID =范围（"TrlClientID"）。文本
'从表格中获取MS-Translator-Access-Data Config
TrlAppName = Range（" TrlAppName"）。Text
TrlClientSecret = Range（"TrlClientSecret"）。Text
TrlRedirectURI = Range（"TrlRedirectURI"）。Text
TrlAppDescr = Range（" TrlAppDescr"）。Text
TrlURICheckOAUTH =范围（"TrlURICheckOAUTH"）。文本
'请求新的AccessToken
设置webRequest = CreateObject（" MSXML2.XMLHTTP"）
sRequest =" grant_type = client_credentials" &安培; _
"& client_id =" &安培; TrlClientID& _
"& client_secret =" &安培; URLEncode（TrlClientSecret）& _
"& scope = http://api.microsofttranslator.com"
webRequest.Open" POST"，TrlURICheckOAUTH，False
webRequest.setRequestHeader" Content-Type"，" application / x-www-form-urlencoded"
webRequest.send sRequest
'处理网络答案
RTArr =拆分（webRequest.responseText，"，"）
RTTokenType = RTArr（FindArrEl（RTArr，" token_type"） ;））
RTAccessToken = RTArr（FindArrEl（RTArr，" access_token"））
RTExpiresIn = RTArr（FindArrEl（RTArr，" expires_in"））
RTScope = RTArr（FindArrEl（RTArr） ，"scope"，"）
如果是InStr（1，RTAccessToken，"错误"）> 0然后_
Err.Raise 9999 ,,"Sub：GetAccessToken"" &安培; vbCrLf _
& "RTTokenType：" &安培; RTTokenType& vbCrLf _
& "RTAccessToken：" &安培; RTAccessToken& vbCrLf _
& "RTExpiresIn：" &安培; RTExpiresIn& vbCrLf _
& "RTScope：" &安培; RTScope
设置webRequest = Nothing
sAccess_Token = PairValue（RTAccessToken）
'通过将10％corresp的安全边际设置为大约1 Min
dtExpiry_Time = Now来确定到期日期（ ）+ Val（PairValue（RTExpiresIn））/ 60/60/24 * 0.9
'在表格中保存新的Expire-Time和AccessToken
范围（"TrlExpirationTime"）。Value = dtExpiry_Time
范围（"TrlAccessToken"）。值= sAccess_Token
结束如果
GetAccessToken = sAccess_Token
结束函数
错误描述Windows 8 64位/ Office 2013 32位
上面的代码创建编译时错误 - 未声明的用户定义类型 - 看似合乎逻辑，因为msxml6.dll不包含a .XMLHTTP方法。
将Dim webRequest更改为MSXML2.XMLHTTP为Dim webRequest为MSXML2.XMLHTTP60
和Set webRequest = CreateObject（" MSXML2.XMLHTTP" ）设置webRequest = CreateObj ect（" MSXML2.XMLHTTP60"）
导致错误429：active-X组件无法创建对象。
更改为晚期绑定与<
"Dim webRequest as object" und"Set webRequest = CreateObject（" MSXML2.XMLHTTP"）"
导致错误-2147024809 - "翻译错误 - 连接失败？错误的参数"在指令webRequest.send上 - 但.send字符串参数的内容与其他工作系统上的内容完全相同。
更改为以
为单位的后期绑定"Dim webRequest as object" und"Set webRequest = CreateObject（" MSXML2.XMLHTTP60"）""
导致错误429：active-X组件无法创建对象。
观察
在华硕N56VZ上，Excel IDE中没有相同的msxml6.dll"参考资料"与在XP / Office 2003上一样 - 比较IDE的对象资源管理器中的可用方法。华硕一个只有一个XMLHTTP方法 - XMLHTTP60，XP有几个：
XMLHTTP，..26，.. 30，.40，.. 60。华硕上的一个来自目录c：\windows \ SysWoW64 \msxml6.dll - XP上的那个来自c：\windows \ system32 \msxml6.dll。
通过右键单击资源管理器和"功能"中引用的c：\windows \ sysWOW64 \msxml6.dll的版本"详情"是：MSXML 6.0,6.30.9200.16447,1.71MB，1.11.2012 - 根据 （链接1 不接受作为输入） 这是一个
32位版本的Windows 8 - 这似乎没问题。
阅读wow64 32位仿真器（link2不被接受为输入）
似乎没问题，在目录c：\windows \ SysWoW64应该是32位dll！
但为什么它的方法与XP上的方法不同。根据 （link2未被接受为输入）第2页"由于与64位系统组件共享存储器，已经为Wow64.dll修改了一些32位DLL"。 - 这可能是
的方法差异的原因吗？
其他dll没问题：测试正则表达式选择工作正常 - 没有一般的dll问题。
问题
哪种方法是工作的XMLHTTP或XMLHTTP60
如果是XMLHTTP是怎么回事选择msxml6.dll
64位系统中的.send参数是否必须不同？如果是，则此信息在哪里？
是否缺少.setRequestHeader参数
......或者我的问题的解决方案是什么
2013-03-04 10:15 GMT - 临时解决方案
经过一些试验和错误后，将VBE IDE中的引用更改为：c：\windows \ sysWOW64 \msxml3.dll一切正常。问题仍然存在：微软的意图是什么 - 每个人在64位系统中使用MSXML2.XMLHTTP与
Windows 8和Office 2013（32位）都必须更改默认参考c：\windows \\上面提到的\\ sysWOW64 \msxml6.dll - 以及后期绑定如何找到合适的msxml？.dll？
欢迎来自MS的任何评论！
Link1：http： - / - / support.microsoft.com- / kb- / 2757638- / de
Link2： http： - / - / www.techsupportalert .com- / content- / how-windows7-vista64-support-32bit-applications.htm
替换 - /用/
Hans-Ulrich - 看起来你已经去过能够解决问题的翻译部分。在VBA论坛中可能会更好地询问有关msxml的问题的最佳场所吗？
http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads
While working fine on XP/Excel 2003, Vista/Excel 2007, W7/Excel 2007, XP/Excel 2010 - all 32bit systems and applications - the code for getting the time-limited translator-access-code does not work on the 64bit Windows 8 system with Office 32bit.
Main function of original code: (based on solution from Patrick O'Beirne (sysmod dot com)
Function GetAccessToken() As String
'The validity of the AccessToken is checked and if it's time has expired a new one is requested
'Beforehand you must get your "Client ID" and "Client Secret" from
'https://datamarket.azure.com/developer/applications
'Webrequest definitons
Dim sRequest As String
Dim webRequest As MSXML2.XMLHTTP
Dim TrlClientID As String, TrlAppName As String, TrlClientSecret As String
Dim TrlRedirectURI As String, TrlAppDescr As String, TrlURICheckOAUTH As String
'Webrequest response text definitions
Dim RTArr As Variant, RTTokenType As String, RTAccessToken As String, RTExpiresIn As String, RTScope As String
Static sAccess_Token As String, dtExpiry_Time As Date
'Get "old" Expire-Time and AccessToken from Sheet Config
dtExpiry_Time = Range("TrlExpirationTime").Value
sAccess_Token = Range("TrlAccessToken").Value
'If AccessToken timed out get new one
If Now() > dtExpiry_Time Then
TrlClientID = Range("TrlClientID").Text
'Get your MS-Translator-Access-Data from sheet Config
TrlAppName = Range("TrlAppName").Text
TrlClientSecret = Range("TrlClientSecret").Text
TrlRedirectURI = Range("TrlRedirectURI").Text
TrlAppDescr = Range("TrlAppDescr").Text
TrlURICheckOAUTH = Range("TrlURICheckOAUTH").Text
'Request a new AccessToken
Set webRequest = CreateObject("MSXML2.XMLHTTP")
sRequest = "grant_type=client_credentials" & _
"&client_id=" & TrlClientID & _
"&client_secret=" & URLEncode(TrlClientSecret) & _
"&scope=http://api.microsofttranslator.com"
webRequest.Open "POST", TrlURICheckOAUTH, False
webRequest.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
webRequest.send sRequest
'Process the web answer
RTArr = Split(webRequest.responseText, ",")
RTTokenType = RTArr(FindArrEl(RTArr, "token_type"))
RTAccessToken = RTArr(FindArrEl(RTArr, "access_token"))
RTExpiresIn = RTArr(FindArrEl(RTArr, "expires_in"))
RTScope = RTArr(FindArrEl(RTArr, "scope"))
If InStr(1, RTAccessToken, "error") > 0 Then _
Err.Raise 9999, , "Sub: GetAccessToken " & vbCrLf _
& "RTTokenType: " & RTTokenType & vbCrLf _
& "RTAccessToken: " & RTAccessToken & vbCrLf _
& "RTExpiresIn: " & RTExpiresIn & vbCrLf _
& "RTScope: " & RTScope
Set webRequest = Nothing
sAccess_Token = PairValue(RTAccessToken)
'Determine the expire-date by conidering a safety margin of 10% corresp to approx 1 Min
dtExpiry_Time = Now() + Val(PairValue(RTExpiresIn)) / 60 / 60 / 24 * 0.9
'Save new Expire-Time and AccessToken in sheet Config
Range("TrlExpirationTime").Value = dtExpiry_Time
Range("TrlAccessToken").Value = sAccess_Token
End If
GetAccessToken = sAccess_Token
End Function
ERROR DESCRIPTION Windows 8 64Bit / Office 2013 32bit
Above code creates a compile time error - user defined type not declared - seems logical because msxml6.dll does not include a .XMLHTTP method.
Changing Dim webRequest As MSXML2.XMLHTTP to Dim webRequest As MSXML2.XMLHTTP60
and Set webRequest = CreateObject("MSXML2.XMLHTTP") to Set webRequest = CreateObject("MSXML2.XMLHTTP60")
leads to Error 429: Object creation not possible by active-X component.
Changing to late binding with
"Dim webRequest as object" und "Set webRequest = CreateObject("MSXML2.XMLHTTP")"
leads to Error -2147024809 - "Translation error - Connection failure? Wrong parameter" on instruction webRequest.send - but the content of the .send string parameter is exactly the same as on the other working systems.
Changing to late binding with
"Dim webRequest as object" und "Set webRequest = CreateObject("MSXML2.XMLHTTP60")"
leads to Error 429: Object creation not possible by active-X component.
OBSERVATIONS
On Asus N56VZ there is not the same msxml6.dll in the Excel IDE "References" as on the XP/Office 2003 - when comparing the available methods in the IDE's Object Explorer. The Asus one has only one XMLHTTP method - XMLHTTP60, the XP has several: XMLHTTP, ..26, ..30, ..40, ..60. The one on the Asus comes from directory c:\windows\SysWoW64\msxml6.dll - the one on the XP comes from c:\windows\system32\msxml6.dll.
The version of the referenced c:\windows\sysWOW64\msxml6.dll via right click in explorer and "features" "Details" is: MSXML 6.0, 6.30.9200.16447, 1.71MB, 1.11.2012 - accoding to (link 1 not accepted as input) this is a 32 Bit Version for Windows 8 - which seems to be ok.
Reading about the wow64 32-Bit Emulator (link2 not accepted as Input)
it seems ok, that in the directory c:\windows\SysWoW64 should be the 32-Bit dll !
But why are it's methods different from the ones on the XP. According to (link2 not accepted as Input) page 2 "some 32-bit DLL's have been modified for Wow64.dll due to sharing memory with 64-bit system components" - may this be the reason for the differences in methods?
No problem with other dll's: testing regex selection works fine - no general dll problem.
QUESTIONS
Which method is the working one XMLHTTP or XMLHTTP60
If it is XMLHTTP how is the proper msxml6.dll selected
Does the .send parameter have to be different in a 64bit system and if yes where is this information
Are there any .setRequestHeader parameters missing
... or what is the solution to my problem
2013-03-04 10:15 GMT - TEMPORARY SOLUTION
After some Trial and Error and changing the reference in the VBE IDE to: c:\windows\sysWOW64\msxml3.dll everything works fine again. The question remains: is that what Microsoft intended it to be - everybody using MSXML2.XMLHTTP in a 64-Bit System with Windows 8 and Office 2013 (32Bit) does have to change the default reference c:\windows\sysWOW64\msxml6.dll to the above mentioned - and how should late binding find the proper msxml?.dll?
Any comment from MS is welcome!
Link1: http:-/-/support.microsoft.com-/kb-/2757638-/de
Link2: http:-/-/www.techsupportalert.com-/content-/how-windows7-vista64-support-32bit-applications.htm
Replace -/ with /
Hans-Ulrich - it looks like you have been able to resolve the Translator part of the issue. The best venue to ask your question related to msxml might be better asked in the VBA forum?
http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads
这篇关于MSXML2.XMLHTTP EXREL VBA GET-AZURE-TOKEN代码错误W8 64位华硕N56VZ带办公用品2013 32BIT用于MICROSOFT TRANSLATOR SERVICE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
