Open Excel file in SharePoint through VBA

Question

I'm trying to open an excel file in SharePoint through a vba code in another local excel file. However, it gives me a Dialog Box and lets me save the file instead of directly opening in Excel.

Below is the code I used. Would be very grateful for your help. Thanks.

Dim wb AS Workbook
FilePath = "https://company.sharepoint.com/sites/Folder1/Folder2/Filename.xlsm"
Set wb = Workbooks.Open(Filename:=FilePath, UpdateLinks:=0)

Have you tried specifying read-only when opening the file? Do you need to open it in edit mode? — Tim Williams
Hi Tim. I need to open it in edit mode. Actually, I have tried running the code in 2 computers (meaning 2 different accounts of Sharepoint as well). In 1 computer, the Sharepoint file will automatically open in Excel. While the other computer would let the user save the sharepoint file as another copy (problem above). I'm not sure if this is related to the Sharepoint account settings, or Excel settings, or my code lacks something else for it to run in any computer. — Tiger129

tmskipper86 tmskipper86 · Accepted Answer · 2021-06-21T01:54:26

I had this issue also. First, you have to fix something in the excel options. So, go to File > Options > Advanced > then scroll down until you see Link Handling > then select the box that says "Open Supported hyperlinks to Office in Office Desktop Apps". Next, I used the coding below to get the link to work. you just need to reply the part of the coding with you sites url sharepoint file. the only issue I came up with is that you have to run the coding below, wait for the file to completely load, and then you can run macros on this file.

Sub OPEN_YRLY()
'
' OPEN_YRLY Macro
'

'
    Range("C1").Select
    ActiveWorkbook.FollowHyperlink Address:="https://aramark365-my.sharepoint.com/:x:/r/personal/......", NewWindow:=False, AddHistory:=True
End Sub

Open Excel file in SharePoint through VBA

1 Answers