Excel VBA URLDownloadToFile Does Not Retrieve Full File (Sharepoint API)

Question

I am trying to link Sharepoint 365 into an Excel Workbook. Previously, I was using wshell to mount the Sharepoint drive to my computer and access everything locally. Sharepoint 365 doesn't allow you to do that, so I am using the API. Here are my steps:

1. Login to Sharepoint and get an access token (OAuth2 handshake)
2. Search for a file or navigate Sharepoint List/Folder/File tree to find a file (this is done through various Sharepoint API calls returning the relevant objects I am looking for)
3. Download the file from Sharepoint onto the local drive (read only operations at the moment)
4. There are a bunch of automated procedures I have been using to interact with data downloaded from various files, this will not change.

With the Sharepoint 365 API, I am stuck at step 3.

I'm using a class to instantiate my Sharepoint session and keep track of my file object. My unit test looks like this:

Sub testDownload()

Dim spFile As New sp365
Dim reqObj As Object
Dim jsonObj As Object
Dim dlStatus As Long

'first log in
spFile.login

'now get a request object that contains filenames and their relative URLs
Set reqObj = spFile.testQuery

'extract the info to a JSON object
Set jsonObj = jsonify(reqObj.responseText, "")

'hardcoding these parameters for now because I just want to download this one file
Debug.Print "Filename: " & jsonObj("d.results(0).Name")
Debug.Print "Relative Url: " & jsonObj("d.results(0).ServerRelativeUrl")

dlStatus = spFile.downloadTemporaryFile(jsonObj("d.results(0).ServerRelativeUrl"), jsonObj("d.results(0).Name"))
If dlStatus = 0 Then
    Debug.Print "File Created"
Else
    Debug.Print "File not created. Status = " & dlStatus
End If

out:
    Exit Sub

End Sub

The relevant code here lies in downloadTemporaryFile. Obviously, I am using the windows urlmon code, which seems to be the de facto way to download files in Excel:

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

And downloading the (temporary) file is here:

Function downloadTemporaryFile(relativeUrl As String, fileName As String) As Boolean
On Error GoTo errHandler
    'Download a file to a temporary path
    'Keep the file inside the class object until it is closed
    
    Dim userPath As String
    Dim filePath As String
    Dim url As String
    Dim wshShell As Object

    'Get the windows shell version for special folders
    Set wshShell = CreateObject("WScript.Shell")
    'get the documents folder for this computer
    userPath = wshShell.SpecialFolders("MyDocuments")
    
    'all logs are stored in <user>/Documents/logs
    filePath = userPath & SHAREPOINT_TEMP_FOLDER
    'Check if the 'SharepointTemp' folder exists
    'if not, create the directory
    If Dir(filePath, vbDirectory) = "" Then MkDir (filePath)
    
    'Extract the site (this can differ based on the connected app)
    'FYI: TENANT_DOMAIN is obtained during the Sharepoint Login procedure
    url = "https://" & TENANT_DOMAIN & relativeUrl
    
    'download it now
    Debug.Print "Downloading to: " & filePath & fileName
    Debug.Print "Downloading from: " & url
    downloadTemporaryFile = URLDownloadToFile(0, url, filePath & fileName, 0, 0)
    
out:
    Exit Function
errHandler:
    logDump "Error", "sp365.downloadTemporaryFile", Err.Number & ";" & Err.source & ";" & Err.description
End Function

So this seems like it would work, and URLDownloadToFile returns 0 (S_OK). But only a tiny part of the file is in my download folder. The file I am trying to download in the example is 2MB, the file in my folder is only 4kb and it won't open. I haven't even gotten to cksum yet, but of course it would fail. I've tried other Sharepoint download links (like .linkingUrl and .linkingUri), but I get the same result. When I paste the url the way I have constructed it above into my browser, the file downloads just fine.

Edit: The file is actually an HTML file. It looks like this:

<html><head><title>Working...</title>
</head><body><form method="POST" name="hiddenform" action="https://keysighttech.sharepoint.com/_forms/default.aspx">
<input type="hidden" name="code"  value="..." />
<input type="hidden" name="id_token" value= "..."/>
<input type="hidden" name="session_state" value= "..." />
<input type="hidden" name="correlation_id" value="..."/>
<noscript><p>Script is disabled. Click Submit to continue.</p>
<input type="submit" value="Submit" /></noscript></form>
<script language="javascript">document.forms[0].submit();</script></body></html>

How can I proceed with the download? Any suggestions?

Thank you in advance!

Answer 1

I figured it out. Basically, the UrlDownloadToFile routine does not pass any authentication along with it. So when I send a request for a file, either I get a 401 Unauthorized, an error which basically just spits my request back to me, or the "hint" i posted above, which basically is a redirect with all of the tenant and authentication methods. So instead, I went ahead get authorized and included the headers that I usually use with standard Sharepoint API requests and it returned the file to me in a bit stream. The final class function looks something like this:

Dim url As String
Dim filePtr As Long
Dim oResp() As Byte 'byte array to store the response object
Dim reqObj As Object

'make sure we can navigate to the right folder on people's computers
Dim userPath As String
Dim filePath As String
Dim wshShell As Object

Dim reqKey() As String
Dim reqVal() As String

'Get the windows shell version for special folders
  Set wshShell = CreateObject("WScript.Shell")
'get the documents folder for this computer
  userPath = wshShell.SpecialFolders("MyDocuments")

filePath = userPath & SHAREPOINT_TEMP_FOLDER
'Check if the 'SharepointTemp' folder exists
'if not, create the directory
  If Dir(filePath, vbDirectory) = "" Then MkDir (filePath)

reqKey = sharepointHeadersKeys
reqVal = sharepointHeadersVals

'Extract the site (this can differ based on the connected app)
  url = relativeUrl & SHAREPOINT_BINARY_REQUEST

Set reqObj = getRequest(url, bearer:=AuthToken.item("access_token"), key:=reqKey, value:=reqVal, blnAsync:=True)

'now the file should be in reqObj
  oResp = reqObj.responseBody
'Create a local file and save the results
  filePtr = FreeFile
  Debug.Print "Downloading to: " & filePath & fileName
  If Dir(filePath & fileName) <> "" Then Kill filePath & fileName
  Open filePath & fileName For Binary As #filePtr
  Put #filePtr, , oResp
  Close #filePtr

Now I can use the file from my temp folder as I was before. I am using the metadata.uri returned from the API call and associated with the file object that I queried. This seems to me to be the easiest and cleanest way to do it - especially because I can search the file binary if I am looking for specific text or keywords and save the overhead of opening the file at all. But, of course, I am open to other methods and suggestions.