Excel VBA read from a closed SharePoint file not working

user_7889517 0
Viewed 79

I am trying access a .xlsx file stored in a folder on my team's SharePoint site using this procedure:

Sub GetDataFromExclFileOnSP()
    Const SPURL As String = "https://contoso.sharepoint.com/teams/myTeam/"
    Const SPFld As String = "mySpFolderName"
    Const myFileName As String = "mySpFileName.xlsx"
    Dim srcFileStr As String
    Dim cn As Object
    
    Set cn = CreateObject("ADODB.Connection")
    srcFileStr = GetWebDavStr(SPURL & "/" & SPFld & "/" & myFileName)
    'GetWebDavStr converts the SP url string into a WebDav string
    '\\contoso.sharepoint.com@ssl\teams\myTeam\mySpFolderName\mySpFileName.xlsx
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & srcFileStr & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    'rest of code
End Sub

If i don't have the SharePoint site open in Internet Explorer the moment i run this, it fails on the "cn.Open" line, with this error message:

enter image description here

Interestingly, having the site open in any other browsers (including my default browser) makes no difference, the "cn.Open" line likes IE and IE only.

Not sure if it's related, but similarly to the above behavior, i am unable to map this SP folder using Windows Explorer's "Map network drive" if the site isn't open in IE at the time.

Any ideas what's causing this and how to prevent this?

I can work around it by calling another procedure before this one, that opens the SP site in a hidden IE window and closes it afterwards, but, naturally, I'd like to avoid having to do this.

sharepoint vba excel
0 Answers
Related