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 na...
developer
excel
Excel for web
Macros and VBA
office 365
NikolinoDE's avatar
NikolinoDE
Platinum 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
    Platinum 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