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

4 Replies

  • 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.

    • GeorgieAnne's avatar
      GeorgieAnne
      Iron Contributor
      Sep 22, 2025

      Thank You NikolinoDE​ 

      How can I make the ' Open directly from SharePoint open in the Desktop application and not the browser?

      Once the workbook is opened in the browser (Edge in that case), VBA stops working!!!

      Thanks in advance...

      GiGi

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

        When you grab a “copy link” from Teams/SharePoint, that’s a sharing link meant to open in the browser (Excel Online). VBA can’t run there.

        If you want to open the file directly in Excel Desktop via VBA, you need to use the native SharePoint/OneDrive path instead of the web “sharing link”.

         

        Sharing link:
        Looks like https://tenant.sharepoint.com/:x:/r/sites/...
        → opens in browser, no VBA.

        Direct SharePoint/OneDrive path:
        Looks like https://tenant.sharepoint.com/sites/TeamName/Shared Documents/General/Test.xlsx
        → opens in Excel Desktop when used with Workbooks.Open

        Or, if you have the OneDrive sync client running, you can use the local sync path like:

        C:\Users\<you>\OneDrive - Contoso\TeamName\General\Test.xlsx

        That way, Excel opens the real file in Desktop and your VBA works normally.

         

        Example VBA: Open in Desktop and Save Copy.

        Sub SaveFromSharePoint()
    Dim wb As Workbook
    Dim localPath As String
    Dim spPath As String
    
    ' Local path for saving a copy
    localPath = Environ("USERPROFILE") & "\Downloads\Test.xlsx"
    
    ' Use the full direct SharePoint document library URL
    spPath = "https://contoso.sharepoint.com/sites/TeamName/Shared Documents/General/Test.xlsx"
    
    ' Open in Desktop Excel
    Set wb = Workbooks.Open(spPath)
    
    ' Save a copy locally
    wb.SaveCopyAs localPath
    wb.Close SaveChanges:=False
    
    MsgBox "Saved to: " & localPath, vbInformation
End Sub

        To get the direct URL:

        Go to the document library in SharePoint (not just Teams chat → Files).

        Right-click the file → Details → Copy the full path from the properties pane.
        (It should end in .xlsx and not have ?web=1 at the end).

         

        Hope this will help you.

Resources