Working with workbooks shared via Teams/Sharepoint

The URL you’re passing to URLDownloadToFile is not the direct file stream, but rather the HTML “viewer” link (or a redirect page). That’s why you’re getting a 4KB file (basically an HTML stub) instead of the actual .xlsx binary.

Teams and SharePoint don’t hand over the raw file when you just grab the “copy link” from the UI.

Instead, you get a sharing link that requires authentication and resolves via an HTML page.

URLDownloadToFile doesn’t handle authentication cookies or redirects, so it saves that HTML instead of the file.

Since you’re already working in Excel Desktop with Teams/SharePoint files, the simplest and most reliable method is:

Sub SaveFromSharePoint()
    Dim wb As Workbook
    Dim localPath As String
    
    localPath = Environ("USERPROFILE") & "\Downloads\Test.xlsx"
    
    ' Open directly from SharePoint
    Set wb = Workbooks.Open("https://contoso.sharepoint.com/sites/TeamName/Shared Documents/General/Test.xlsx")
    
    ' Save a copy locally
    wb.SaveCopyAs localPath
    wb.Close SaveChanges:=False
    
    MsgBox "Saved to: " & localPath, vbInformation
End Sub

That way you skip the URLDownloadToFile issue entirely.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.