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`