Downloading a file from SharePoint using VBA results in a corrupt file

Hi working from the above question I have been trying to download a file from our Teams Sharepoint to a server location on our Local Network. However, the file is not opening correctly, even before I try and download it (see my code below). The excel file does open but no data or grid is displayed

My observation is my file link looks different from the example, but as it is generated by the "Copy Link" button in sharepoint I am assuming (which can be dangerous) it is ok. I can open the file manually

The other observation is that another user maybe accessing the file at the same time

Any advice please or suggestions.

The aim of the macro is to open a sheet, remove any filters other users may have put on the export to a local network as a csv (The file will be picked up by bcp for SQL server)

My starting code is

    Option Explicit

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


Sub Get_BuyerComments()
Dim i As Integer

'Const strUrl As String = "https://<Changed>.sharepoint.com/:x:/s/PlanningPurchasing/JOB1 comments.xlsx ' does not file file
Const strUrl As String = "https://<Changed>.sharepoint.com/:x:/s/PlanningPurchasing/Eac2D7-rfQlIlxOrHedg7jUBhgkHq2aGRppJ-yxKU4SYTw?e=HcsT3K"
'Const strUrl As String = "https://teams.microsoft.com/l/file/<Changed>-7DAB-4809-9713-AB1DE760EE35?tenantId=7b63345c-30b9-4fa5-913d-94d8c63cf3b9&fileType=xlsx&objectUrl=https://bladonjetslimited.sharepoint.com/sites/PlanningPurchasing/Shared Documents/General/JOB1 comments.xlsx&baseUrl=https://bladonjetslimited.sharepoint.com/sites/PlanningPurchasing&serviceName=teams&threadId=19:[email protected]&groupId=9b8235b6-faed-4285-b95b-07aec3d8763c"
Workbooks.Open Filename:=strUrl
Dim strSavePath As String
Dim returnValue As Long

strSavePath = "\\<Changed>\BPA Exports\Shortage Report\Production\Processing\butercomments.xlsx"
returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)


End Sub

CodePudding user response：

You need to be authenticated to SharePoint (logged in). Your code in this and in the previous topic does not seem to include any kind of authentication.

File is probably not corrupt, it is just "access denied" page you are downloading instead of the file. You can check the content of your file using notepad for example.

URLDownloadToFile is great for internet download, but you need to authenticate user against SharePoint. This is not trivial with VBA. I would recommend you use some tools that are more appropriate for the task instead of VBA, like power automate for example.