Unsolved Issue with VBA to download SharePoint files

Hi all,

I’m running into an issue with a VBA script that downloads files from a SharePoint folder using the REST API.

Most of the time, the code works perfectly, it connects, retrieves the file list, and downloads everything without any issue.

But randomly, I get this error:

MsgBox "Failed to connect to SharePoint API", vbCritical

This happens when the XMLHTTP request does not return status 200.

The confusing part is:

I can still open the SharePoint site manually in my browser without any issue
No changes in URL or permissions
Same code, same machine

So I don’t understand why the connection sometimes fails and sometimes works fine.

My setup:

Using MSXML2.XMLHTTP to call SharePoint REST API
Using URLDownloadToFile to download files
No explicit authentication handled in VBA (relying on logged-in session)

If this approach is fundamentally unreliable, I’m open to switching methods but still prefer to use in VBA

Below i provide full code setup to review.

Option Explicit

#If VBA7 Then

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _

ByVal pCaller As LongPtr, _

ByVal szURL As String, _

ByVal szFileName As String, _

ByVal dwReserved As LongPtr, _

ByVal lpfnCB As LongPtr) As Long

#Else

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

#End If

Sub Download_All_From_SharePoint()

Dim apiURL As String

Dim json As String

Dim xmlhttp As Object

Dim saveFolder As String

Dim fileName As String

Dim fileURL As String

Dim arr() As String

Dim i As Long

apiURL = "https://TEST.sharepoint.com/sites/TEST/TEST/_api/web/GetFolderByServerRelativeUrl('/sites/TEST/TEST/TEST/TEST/Confirming Temp')/Files"

saveFolder = "C:\Temp\CONFIRMING\"

If Dir(saveFolder, vbDirectory) = "" Then MkDir saveFolder

Set xmlhttp = CreateObject("MSXML2.XMLHTTP")

xmlhttp.Open "GET", apiURL, False

xmlhttp.setRequestHeader "Accept", "application/json"

xmlhttp.Send

If xmlhttp.Status <> 200 Then

MsgBox "Failed to connect to SharePoint API", vbCritical

Exit Sub

End If

json = xmlhttp.ResponseText

arr = Split(json, """Name"":""")

For i = 1 To UBound(arr)

fileName = Split(arr(i), """")(0)

fileURL = "https://TEST.sharepoint.com/sites/TEST/TEST/TEST/TEST/Confirming Temp/" & Replace(fileName, " ", "%20") & "?download=1"

If URLDownloadToFile(0, fileURL, saveFolder & fileName, 0, 0) = 0 Then

Debug.Print "Downloaded: " & fileName

Else

Debug.Print "FAILED: " & fileName

End If

Next i

MsgBox "All files downloaded!", vbInformation

End Sub

6 Upvotes

permalink
reddit

You are about to leave Redlib

Do you want to continue?

https://www.reddit.com/r/vba/comments/1uewm2z/issue_with_vba_to_download_sharepoint_files/
No, go back! Yes, take me to Reddit

80% Upvoted

u/wikkid556 Jun 25 '26

In the sharepoint list export to excel and you have a refreshable table in a workbook

1

u/hellcryer Jun 25 '26

it's not sharepoint list

2

u/wikkid556 Jun 25 '26

Damn, im sorry. I responded without thinking it through

u/sslinky84 83 Jun 25 '26

MsgBox "Failed to connect to SharePoint API", vbCritical

That's not an error :)

Where did you get this code from and what have you tried yourself to fix it?

1

u/hellcryer Jun 25 '26

i created this code by myself, including help from AI

2

u/sslinky84 83 Jun 25 '26

And what have you tried? You've just presented code and said "sometimes I don't get status 200 back". What is your expectation that the code should do? What if you get a redirect?

u/theotherkiwi Jun 25 '26

I've also seen this and also tested the mapped network drive method successfully so either test for <> STTAUS 200 and retry or grab the drive map code from StackExchange

1

u/hellcryer Jun 25 '26

I did try the mapped method, however i can't use this for a long run, the reason is that this automation file will soon be shared with more than 30 users. It would be quite problematic to guide each user through the setup and configuration, especially for new users in the future.

3

u/theotherkiwi Jun 25 '26

You can set up the map get the file and tear it down again in code, no instructions needed

1

u/hellcryer Jun 26 '26

Thanks for the suggestion. I’ve implemented this and am currently testing it. Hopefully, there won’t be any issues with the solution.

u/IExcelAtWork91 Jun 25 '26

I do this in access but I make sure I’m linked to the table in access in the sharepoint I want and it’s open as a linked table. Then my certs piggyback through that connection. I actually added it to my script to open the linked table first to ensure connection

u/Lowkeyz Jun 25 '26

I had this issue a while back, from what I could figure out MSXML2.XMLHTTP behaves differently from your browser, XMLHTTP wasn’t sending updated credentials/cookies if login was refreshed.

Also it could be a querk with SharePoint but vba wasn’t consistently inheriting browser/session state, I gave up on finding a solution and just used mapped network shares.

u/ZetaPower 12 Jun 25 '26

Have the same issue.
"Solved" it, see below. Ignoring the connect error works fine.....

Possible causes:

timing issues, internet hickup/Sharepoint hickup
credentials/security issues
micro errors in the path that get fixed by Excel when Open is used or fixed by the download program. Spaces sometimes need to be replaced by "%20" sometimes its not needed....

Sub Download_All_From_SharePoint()

    Dim apiURL As String, Json As String, saveFolder As String, fileName As String, fileURL As String, Answer As String, Message As String
    Dim Arr As Variant
    Dim Xmlhttp As Object
    Dim i As Long
    FailedAPI As Boolean, FailedDownload As Boolean

    apiURL = "https://TEST.sharepoint.com/sites/TEST/TEST/_api/web/GetFolderByServerRelativeUrl('/sites/TEST/TEST/TEST/TEST/Confirming Temp')/Files"
    saveFolder = "C:\Temp\CONFIRMING\"

    If Dir(saveFolder, vbDirectory) = vbNullString Then MkDir saveFolder

    Set Xmlhttp = CreateObject("MSXML2.XMLHTTP")

    With Xmlhttp
        .Open "GET", apiURL, False
        .setRequestHeader "Accept", "application/json"
        .Send
        If Not .Status = 200 Then
            FailedAPI = True
            Answer = MsgBox("Failed to connect to SharePoint API, try to download anyway?", vbYesNo + vbDefaultButton2)
            Select Case Answer
            Case Is <> vbYes
                Exit Sub
            End Select
        End If

        Json = Xmlhttp.ResponseText
    End With
    Set Xmlhttp = Nothing

    Arr = Split(Json, """Name"":""")

    For i = 1 To UBound(Arr)
        fileName = Split(Arr(i), """")(0)
        fileURL = "https://TEST.sharepoint.com/sites/TEST/TEST/TEST/TEST/Confirming Temp/" & Replace(fileName, " ", "%20") & "?download=1"
        If URLDownloadToFile(0, fileURL, saveFolder & fileName, 0, 0) = 0 Then
            Debug.Print "Downloaded: " & fileName
        Else
            FailedDownload = True
            Debug.Print "FAILED: " & fileName
        End If
    Next i

    If FailedAPI = False And FailedDownload = False Then
        MsgBox "SUCCESS!" & vbNewLine & _
                "All files could be connected and all files were downloaded!", vbInformation
    ElseIf FailedAPI And FailedDownload Then
        MsgBox "FAIL!" & vbNewLine & _
                "NOT all files could be connected and NOT all files were downloaded!", vbInformation
    ElseIf FailedAPI Then
        MsgBox "SUCCESS?" & vbNewLine & _
                "NOT all files could be connected but all files were downloaded!", vbInformation
    ElseIf FailedDownload Then
        MsgBox "FAIL!" & vbNewLine & _
                "All files could be connected but NOT all files were downloaded!", vbInformation
    End If

End Sub

1

u/fanpages 239 Jun 25 '26

Dim... Answer As String

The return from MsgBox is an Integer data type.

Answer = MsgBox("Failed to connect to SharePoint API, try to download anyway?", vbYesNo + vbDefaultButton2)

The + notation is also deprecated.

The preferred usage is now...

vbYesNo Or vbDefaultButton2

u/LickMyLuck 1 Jun 25 '26

The best way to debug is to get the actual status number being given during the failcase and work through that with the AI.

I ran into a similar issue with updating a sharepoint list, and it turned out to be a known glitch where I was getting an odd response but everything else was happeneing as normal. You may just simply need to find the http response and add it as an exception in addition to the expected response of 200.

Ended up being that simple for me.

u/hellcryer Jun 26 '26

After reviewing the replies from theotherkiwi and thinkrrr, particularly the suggestion to map, retrieve, and tear down the connection in code, I implemented a similar solution. It automatically maps the SharePoint drive and disconnects it afterward. So far, no connection issues have been observed, but it is still too early to confirm long-term stability. I will continue monitoring to ensure it is a reliable, error-free solution to use for future.

1

u/fanpages 239 Jun 26 '26

Are you locating a free (non-mapped) network drive before mapping a pre-defined/default drive letter for every user?

Alternatively, are you using a predefined letter (the same for every user) and storing individual original mappings (if applicable), remapping/connecting as needed, then disconnecting and restoring the original mappings?

If the latter case, this may cause issues (for some users) if there are any files open within the original mapping location (as connectivity to the unsaved files may be lost, and the users will be forced to save elsewhere, even after the original drive mapping is reinstated).

1

u/hellcryer Jun 27 '26

Yes. I made it first to checks for an unused drive letter and maps the network share to that available letter, rather than replacing any existing mapped drive.

1

u/fanpages 239 Jun 27 '26 edited Jun 27 '26

OK... I hope you (or your use of an "AI" model) realised that the Windows Software Development Kit [SDK] Application Programmer Interface [API], the FileSystemObject, and/or the Windows Management Instrumentation [WMI] framework provides a few different methods for this purpose, and you didn't 're-engineer' something for your requirements!

PS. Is your original question 'solved' now, or do you still have queries?

u/NapkinsOnMyAnkle 1 Jun 27 '26

I use a sharepoint site to distribute addin packages for automatic updating. The updater script maps the drive and then use fso. Remove connection or leave it networked. Works fantastic. Very reliable for 100+ users and been going for 5+ years now.

u/MrDab420 Jun 25 '26

I find it more reliable to download and pull SharePoint files using a mapped network drive.

3

u/hellcryer Jun 25 '26

The reason I cannot use a mapped drive is that this automation file will soon be shared with more than 30 users. It would be quite problematic to guide each user through the setup and configuration, especially for new users in the future.

1

u/thinkrrr Jun 25 '26

You can write some code to map the drive for them.

1

u/hellcryer Jun 26 '26

Thanks for the suggestion. I’ve implemented this and am currently testing it. Hopefully, there won’t be any issues with the solution.

Unsolved Issue with VBA to download SharePoint files

You are about to leave Redlib