Forum Discussion

GeorgieAnne's avatar
GeorgieAnne
Iron Contributor
Sep 20, 2025

Working with workbooks shared via Teams/Sharepoint

Hello Excellers,

 

I need some insight on an issue that I am not sure what the source is...

A) We sometimes share workbooks via Teams. You know when you are in a particular chat and next to the name of the chat at the top of the screen you see Shared and then you see Files button a bit below the Shared menu and when you click on that Files button you will see a list of what workbooks are shared.

B) So I wrote some VBA code to download a copy to the Downloads folder like that:

Dim RetVal As Long

Dim SharePointFileURL As String

Dim LocalDownloadPath As String

Dim FileName As String

    RetVal = URLDownloadToFile(0, SharePointFileURL, LocalDownloadPath & FileName, 0, 0)

If RetVal = 0 Then

        MsgBox "File successfully downloaded to: " & LocalDownloadPath & FileName, vbInformation

This will message be replaced by the code we need to run... but for now I needed an indicator that it finished downloading.

    Else

        MsgBox "Failed to download the file. Please check the URL or your network connection.", vbExclamation

    End If

 

So RetVal is = 0, and I do see the workbook in the \Downloads folder and it has a size of 4KB and the correct Date Modified time stamp,

 

BUT When I try to open the workbook via the Excel desktop application I get the message:

Excel cannot open the file "File name here" because the file format or file extension is not valid. Verify that the file is not corrupted and that the file extension matches the format of the file. 

 

The workbook should be 34KB in size and that 4KB file is not what I need???

 

Any ideas how to manage this, saving a workbook from a SharePoint / Teams file location to the computer so that we can run VBA code on it.

 

GiGi

developer
excel
Excel for web
Macros and VBA
office 365

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Sep 21, 2025

    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.

Resources