locked
MSXML2.XMLHTTP ERROR IN EXCEL VBA GET-AZURE-TOKEN CODE ON W8 64BIT ASUS N56VZ WITH OFFICE 2013 32BIT FOR MICROSOFT TRANSLATOR SERVICE RRS feed

  • Question

  • 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 /

     

     

     


    Wednesday, February 27, 2013 8:45 PM

All replies