[SOLVED] How to Download file from Sharepoint to local drive using VBA in an Excel Macro

How to Download file from Sharepoint to local drive using VBA in an Excel Macro

I'm trying to download a sharepoint file using VBA in an Excel macro. I copied some code from this site but it will not work. It downloads a file but the file will not open properly. The correct file is 28KB but the file it downloads is only 4KB. When trying to open it gives an error message saying the file format or extension is not valid. Verify the file is not corrupted. The file downloads and opens with no problem manually but the code does not work. Any help would be greatly appreciated.

https://sandvik.sharepoint.com/team/PUMebaneDataHub/Shared%20Documents/Forms/Allitems.aspx

Above is the URL where the file is located. The file is: Mebane Incident Intake Form.xlsx

here is the actual copied link from Sharepoint https://sandvik.sharepoint.com/:x:/r/teams/PUMebaneDataHub/Shared%20Documents/Mebane%20Incident%20Intake%20Form.xlsx?d=w8031d09838fe4673854fa5241b259fca&csf=1&web=1&e=ty1IRb

Here is my VBA Code.

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 DownloadFileFromWeb()
    Dim i As Integer
    Const strUrl As String = "https://sandvik.sharepoint.com/teams/PUMebaneDataHub/Shared%20Documents/Mebane Incident Intake Form.xlsx"
    Dim strSavePath As String 
    Dim returnValue As Long 
    strSavePath = "C:\temp1\Mebane Incident Intake Form.xlsx"
    returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0) 
End Sub

see above... i was expecting it to download the file .....but it downloads a garbage file that will not open.

Solution

Easiest approach:

Dim wb As Workbook
Set wb = Workbooks.Open(UrlToWorkbook)
wb.SaveAs savePathHere

If you have problems opening the file try removing the ? and everything following that from the URL.

See https://sharepointcass.com/2021/03/11/what-do-the-parameters-in-a-shared-sharepoint-onedrive-link-mean/ for what those odd parts of the URL (eg) /:x: etc actually mean.