文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >VBA代码:运行时错误'-2147012890 (80072ee6)‘自动化错误

问VBA代码:运行时错误'-2147012890 (80072ee6)‘自动化错误
EN

Stack Overflow用户
提问于 2015-03-17 16:09:14
回答 1查看 5.2K关注 0票数 0

我正在使用以下函数运行Excel，以便使用身份验证将文件上载到sharepoint。

代码语言：javascript
运行
复制
Public Sub CopyToSharePoint()

UserName = "username@sharepoint.com"
    pw = "password"
    sharepointUrl = ""https://corp.sharepoint.com/sites/uat/_layouts/15/start.aspx#/a1docsuat/"

    Set LobjXML = CreateObject("Microsoft.XMLHTTP")
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fldr = fso.GetFolder("c:/vba2sharepoint/")
    For Each f In fldr.Files
    sharepointFileName = sharepointUrl & f.Name
    'commentedout-> If sharepointFileName Like "*.txt" Then
        Set tsIn = f.OpenAsTextStream
        sBody = tsIn.ReadAll
        tsIn.Close
        'commentedout-> Set xmlhttp = CreateObject("MSXML2.XMLHTTP.4.0")
        Set xmlhttp = New MSXML2.XMLHTTP60
        xmlhttp.Open "PUT", sharepointFileName, False, UserName, pw
        xmlhttp.Send sBody
    'commentedout-> End If
Next f

End Sub

运行它时，会收到以下错误消息：“运行时错误”-2147012890(80072ee6)“自动化错误”

我是VBA的新手，欢迎你给我任何建议，谢谢。

vba
excel
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2015-05-28 21:54:24

我能够通过将CopyToSharepoint()函数重新设计为ConnectSharePointOnlineWebPortal来解决这个问题.

代码语言：javascript
运行
复制
Public Function ConnectSharePointOnlineWebPortal(ByVal strEmail As String, ByVal strPassword As String) As String

Dim strPPFT As String
Dim strUnixTime As String

Dim strT As String
Dim strAction As String

ConnectSharePointOnlineWebPortal = "Failed"

Application.ScreenUpdating = True
Sheets("GUI").Range("lblReportMsg") = "Navigating to SharePointOnline website.  Please wait..."
'Application.ScreenUpdating = False
strProxyInfo = GetProxyInfoForUrl("https://login.microsoftonline.com/").proxy
'Set zHttp = CreateObject("WinHTTP.WinHTTPrequest.5.1")
'Set zHttp = CreateObject("Msxml2.ServerXMLHTTP.6.0")

Set zHttp = CreateObject("Microsoft.XMLHTTP")
Set ieDom = CreateObject("htmlfile")


strURL = "https://login.microsoftonline.com/login.srf?"
DeleteUrlCacheEntry (strURL)
zHttp.Open "GET", strURL, False
'If Len(strProxyInfo) > 0 Then
'    zHttp.setProxy 2, strProxyInfo
'End If
'zHttp.SetCredentials "", "", HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
'zHttp.option(WinHttpRequestOption_EnableRedirects) = True
zHttp.setRequestHeader "Accept", "text/html, application/xhtml+xml, */*"
'zHttp.setRequestHeader "Referer", strRefererURL
zHttp.setRequestHeader "Accept-Language", "en-us"
zHttp.setRequestHeader "UA-CPU", "x86"
zHttp.setRequestHeader "Accept-Encoding", "none"
zHttp.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko"
zHttp.setRequestHeader "Host", "login.microsoftonline.com"
zHttp.setRequestHeader "Connection", "Keep-Alive"
zHttp.setRequestHeader "DNT", "1"
zHttp.setRequestHeader "Cache-Control", "no-cache"
'zHttp.setRequestHeader "Proxy-Connection", "Keep-Alive"
zHttp.setRequestHeader "Cookie", "MSPShared=1"
zHttp.Send

If zHttp.Status <> 200 Then
    ConnectSharePointOnlineWebPortal = "Failed"
    Exit Function
End If


If InStr(1, zHttp.responseText, "Sign out") > 0 Then
    RetVal = LogoutSharePointOnlineWebPortal

    strURL = "https://login.microsoftonline.com/login.srf?"
    DeleteUrlCacheEntry (strURL)
    zHttp.Open "GET", strURL, False
    'If Len(strProxyInfo) > 0 Then
    '    zHttp.setProxy 2, strProxyInfo
    'End If
    'zHttp.SetCredentials "", "", HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
    'zHttp.option(WinHttpRequestOption_EnableRedirects) = True
    zHttp.setRequestHeader "Accept", "text/html, application/xhtml+xml, */*"
    'zHttp.setRequestHeader "Referer", strRefererURL
    zHttp.setRequestHeader "Accept-Language", "en-us"
    zHttp.setRequestHeader "UA-CPU", "x86"
    zHttp.setRequestHeader "Accept-Encoding", "none"
    zHttp.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko"
    zHttp.setRequestHeader "Host", "login.microsoftonline.com"
    zHttp.setRequestHeader "Connection", "Keep-Alive"
    zHttp.setRequestHeader "Cache-Control", "no-cache"
    zHttp.setRequestHeader "DNT", "1"
    'zHttp.setRequestHeader "Proxy-Connection", "Keep-Alive"
    zHttp.setRequestHeader "Cookie", "MSPShared=1"
    zHttp.Send

End If

'If InStr(1, zHttp.responseText, strEmail) > 0 Then
'    ConnectSharePointOnlineWebPortal = "Success"
'    Exit Function
'End If

If InStr(1, zHttp.responseText, "User account") = 0 Then
    ConnectSharePointOnlineWebPortal = "Failed"
    Exit Function
End If

ieDom.body.innerhtml = zHttp.responseText

Set ieInp1 = ieDom.getElementByID("PPFT")
If ieInp1 Is Nothing Then
    ConnectSharePointOnlineWebPortal = "Failed"
    Exit Function
End If

strPPFT = ieInp1.Value
strUnixTime = DateDiff("S", "1/1/1970", Now())

strURL = "https://login.microsoftonline.com/GetUserRealm.srf?login=" & modMisc.URLEncode(strEmail) & "&handler=1&extended=1"
DeleteUrlCacheEntry (strURL)
zHttp.Open "GET", strURL, False
'zHttp.SetCredentials "", "", HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
'zHttp.option(WinHttpRequestOption_EnableRedirects) = True
zHttp.setRequestHeader "Accept", "image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/x-shockwave-flash, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/xaml+xml, application/vnd.ms-xpsdocument, application/x-ms-xbap, application/x-ms-application, */*"
strRefererURL = "https://login.microsoftonline.com/"
zHttp.setRequestHeader "x-requested-with", "XMLHttpRequest"
zHttp.setRequestHeader "Accept-Language", "en-us"
zHttp.setRequestHeader "UA-CPU", "x86"
zHttp.setRequestHeader "Accept-Encoding", "none"
zHttp.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.30)"
zHttp.setRequestHeader "Host", "login.microsoftonline.com"
zHttp.setRequestHeader "Connection", "Keep-Alive"
zHttp.setRequestHeader "Cache-Control", "no-cache"
zHttp.setRequestHeader "Proxy-Connection", "Keep-Alive"
zHttp.Send


strURL = "https://login.microsoftonline.com/ppsecure/post.srf?bk=" & strUnixTime
strRefererURL = "https://login.microsoftonline.com/"
strPostBody = "login=" & modMisc.URLEncode(strEmail) & "&passwd=" & modMisc.URLEncode(strPassword) & "&PPSX=PassportR&PPFT=" & modMisc.URLEncode(strPPFT) & "&type=11&LoginOptions=3&NewUser=1&idsbho=1&PwdPad=&sso=&vv=&uiver=1&i12=1&i13=MSIE&i14=8.0&i15=1280&i16=851"
DeleteUrlCacheEntry (strURL)
zHttp.Open "POST", strURL, False
'zHttp.SetCredentials "", "", HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
'zHttp.option(WinHttpRequestOption_EnableRedirects) = True
zHttp.setRequestHeader "Accept", "image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/x-shockwave-flash, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/xaml+xml, application/vnd.ms-xpsdocument, application/x-ms-xbap, application/x-ms-application, */*"
zHttp.setRequestHeader "Referer", strRefererURL
zHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
zHttp.setRequestHeader "Accept-Language", "en-us"
zHttp.setRequestHeader "UA-CPU", "x86"
zHttp.setRequestHeader "Accept-Encoding", "none"
zHttp.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.30)"
zHttp.setRequestHeader "Host", "login.microsoftonline.co"
zHttp.setRequestHeader "Connection", "Keep-Alive"
zHttp.setRequestHeader "Proxy-Connection", "Keep-Alive"
zHttp.setRequestHeader "Content-Length", Len(strPostBody)
zHttp.setRequestHeader "Cache-Control", "no-cache"
'zHttp.setRequestHeader "Cookie", "MSPShared=1; MSPRequ=lt=1427207617&co=1&id=N; MSPOK=$uuid-529756bf-935b-430f-b7e4-b8382610ae72; x-ms-gateway-slice=orgidprod; stsservicecookie=orgidprod"
zHttp.Send strPostBody

If zHttp.Status <> 200 Then
    ConnectSharePointOnlineWebPortal = "Failed"
    Exit Function
End If

If InStr(1, zHttp.responseText, "Sign out") > 0 Then
    ConnectSharePointOnlineWebPortal = "Success"
    Exit Function
End If

'If InStr(1, zHttp.responseText, strEmail) > 0 Then
'    ConnectSharePointOnlineWebPortal = "Success"
'    Exit Function
'End If

ieDom.body.innerhtml = zHttp.responseText
Set ieInp1 = ieDom.getElementByID("fmHF")
If ieInp1 Is Nothing Then
    ConnectSharePointOnlineWebPortal = "Failed"
    Exit Function
End If
strAction = ieInp1.Action

Set ieInp1 = ieDom.getElementByID("t")
If ieInp1 Is Nothing Then
    ConnectSharePointOnlineWebPortal = "Failed"
    Exit Function
End If

strT = ieInp1.Value

strURL = strAction
strRefererURL = "https://login.microsoftonline.com/"
strPostBody = "wbids=0&wbid=MSFT&t=" & modMisc.URLEncode(strT)
DeleteUrlCacheEntry (strURL)
zHttp.Open "POST", strURL, False
'zHttp.SetCredentials "", "", HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
'zHttp.option(WinHttpRequestOption_EnableRedirects) = True
zHttp.setRequestHeader "Accept", "image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/x-shockwave-flash, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/xaml+xml, application/vnd.ms-xpsdocument, application/x-ms-xbap, application/x-ms-application, */*"
zHttp.setRequestHeader "Referer", strRefererURL
zHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
zHttp.setRequestHeader "Accept-Language", "en-us"
zHttp.setRequestHeader "UA-CPU", "x86"
zHttp.setRequestHeader "Accept-Encoding", "none"
zHttp.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.30)"
zHttp.setRequestHeader "Host", "portal.office.com"
zHttp.setRequestHeader "Connection", "Keep-Alive"
zHttp.setRequestHeader "Proxy-Connection", "Keep-Alive"
zHttp.setRequestHeader "Content-Length", Len(strPostBody)
zHttp.setRequestHeader "Cache-Control", "no-cache"
'zHttp.setRequestHeader "Cookie", "MSPShared=1; MSPRequ=lt=1427207617&co=1&id=N; MSPOK=$uuid-529756bf-935b-430f-b7e4-b8382610ae72; x-ms-gateway-slice=orgidprod; stsservicecookie=orgidprod"
zHttp.Send strPostBody

If zHttp.Status <> 200 Then
    ConnectSharePointOnlineWebPortal = "Failed"
    Exit Function
End If

If InStr(1, zHttp.responseText, "Sign out") = 0 Then
    ConnectSharePointOnlineWebPortal = "Failed"
    Exit Function
End If

strURL = "https://portal.office.com/Home"
DeleteUrlCacheEntry (strURL)
zHttp.Open "GET", strURL, False
'zHttp.SetCredentials "", "", HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
'zHttp.option(WinHttpRequestOption_EnableRedirects) = True
zHttp.setRequestHeader "Accept", "image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/x-shockwave-flash, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/xaml+xml, application/vnd.ms-xpsdocument, application/x-ms-xbap, application/x-ms-application, */*"
strRefererURL = "https://login.microsoftonline.com/"
zHttp.setRequestHeader "x-requested-with", "XMLHttpRequest"
zHttp.setRequestHeader "Accept-Language", "en-us"
zHttp.setRequestHeader "UA-CPU", "x86"
zHttp.setRequestHeader "Accept-Encoding", "none"
zHttp.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; InfoPath.2; .NET CLR 3.0.04506.30)"
zHttp.setRequestHeader "Host", "portal.office.com"
zHttp.setRequestHeader "Connection", "Keep-Alive"
zHttp.setRequestHeader "Proxy-Connection", "Keep-Alive"
zHttp.setRequestHeader "Cache-Control", "no-cache"
zHttp.Send

If InStr(1, zHttp.responseText, "Sign out") = 0 Then
    ConnectSharePointOnlineWebPortal = "Failed"
    Exit Function
End If

If InStr(1, zHttp.responseText, strEmail) = 0 Then
    ConnectSharePointOnlineWebPortal = "Failed"
    Exit Function
End If

ConnectSharePointOnlineWebPortal = "Success"

端函数

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/29104123

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档