CODEWITHSUNDEEP
vbasharepointms-worddownload
GMCB
GMCB

Reputation: 337

Download files from sharepoint using VBA access errors due to cookies

I had a file (Theme file) that is stored on Sharepoint and first needed to be downloaded into the temp directory before being loaded applied to word. This worked for a while, but recently I am getting an "access denied error".

I looked around and tested other libraries CreateObject("MSXML2.ServerXMLHTTP.6.0") instead of CreateObject("Microsoft.XMLHTTP").

Interestingly, I don't get the access error message with CreateObject("MSXML2.ServerXMLHTTP.6.0"), but instead it dowloads a page with this error:

[![Screenshot of error message][1]][1]

We can't sign you in Your browser is currently set to block cookies. You need to allow cookies to use this service. Cookies are small text files stored on your computer that tell us when you're signed in. To learn how to allow cookies, check the online help in your web browser.

I hope someone has an idea about why this error occurs and how to solve it

Here is the code I use.

Public Sub Download(ByVal URL As String, ByVal FilePath As String, Optional ByVal Overwrite As Boolean = True)
    
    Dim iOverwrite, oStrm
    If (IsNull(Overwrite) Or Overwrite) Then
        iOverwrite = 2
    Else
        iOverwrite = 1
    End If
    
    Dim HttpReq As Object
    'NOTE: There are some issues downloading if not properly logged in! May need to loggin sharepoint again
    ' https://www.codeproject.com/Questions/1101499/Download-files-from-API-using-vbscript-cmd-prompt
    ' Based on https://stackoverflow.com/questions/22938194/xmlhttp-request-is-raising-an-access-denied-error
    'Set HttpReq = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    Set HttpReq = CreateObject("Microsoft.XMLHTTP")
    'Set HttpReq = CreateObject("MSXML2.ServerXMLHTTP.3.0")
    
    HttpReq.Open "GET", URL, False, "username", "password"
    
    On Error GoTo ErrorHandler
        HttpReq.send
    On Error GoTo 0
    
    If HttpReq.Status = 200 Then
        Set oStrm = CreateObject("ADODB.Stream")
        oStrm.Open
        oStrm.Type = 1
        oStrm.Write HttpReq.responseBody
        oStrm.SaveToFile FilePath, iOverwrite ' 1 = no overwrite, 2 = overwrite
        oStrm.Close
    End If
    
    Exit Sub
    
ErrorHandler:
    MsgBox "The file could not be downloaded. Verify that you are logged in SharePoint with word and browser.", vbCritical, "Download error"
    Debug.Print "Download - Error Downloading file will not be downloaded - Error #: '" & Err.Number & "'. Error description: " & Err.description
End Sub```


  [1]: https://i.sstatic.net/pdH6v.png

Upvotes: 0

Views: 2508

Answers (2)

Gregg Burns
Gregg Burns

Reputation: 333

I use import function specifically designed for this. I use Sharepoint Teams site (no user/password can be sent for auth).

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Function downloadSP(ByVal url As String, ByVal nm As String) As Long
    DownloadFileFromWeb = URLDownloadToFile(0, url, nm, 0, 0) ' nm includes filename
End Function

In addition. I have to first use an ADO query to the sharepoint library directly before. This ADO connection handles authentication and allows subsequent downloads to location. There probably is another method for sending Teams authentication, but this works just fine. (it's also a great way to get data from SP List/Libraries or even within Excel files)

If testConnected Then downloadSP url, nm


Function testConnected() As Boolean
Dim cn As Object
Dim rs As Object
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.RecordSet")

listGUID = "B3657D15-5F5C-468E-B1C2-784B930FE2E6"
siteURL = "https://azuresite.sharepoint.com/sites/test/"
spSql = "Select * from ['https://azuresite.sharepoint.com/sites/test/SL%20Template/Forms/AllItems.aspx']"
cnStr = "Provider=Microsoft.ACE.OLEDB.16.0;WSS;IMEX=2;RetrieveIds=No;DATABASE=" & siteURL & "; LIST=" & listGUID & ";"

cn.ConnectionString = cnStr
On Error GoTo NotConnected
cn.Open
rs.Open spSql, cn, 1, 2
testConnected = True
    cn.Close
    
    Exit Function
NotConnected:
    testConnected = False
    
    Exit Function
End Function

Upvotes: 1

GMCB
GMCB

Reputation: 337

So I managed to solve this issue after:

  1. enabling accepting cookies from: https://login.microsoftonline.com/ and our SharePoint sites (also added them in trusted websites)
  2. Clearing the cookies from the history
  3. Use the "Microsoft.XMLHTTP" library which works (other libraries do not seem to work properly still)

Upvotes: 0

Related Questions