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
– Tim Williams, Commented Mar 10, 2020 at 5:42
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
– Tiger129, Commented Mar 10, 2020 at 5:49

Dharman · Accepted Answer · 2021-06-21 01:59:31Z

1

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

edited Jun 21, 2021 at 1:59

Dharman♦

33.9k27 gold badges103 silver badges157 bronze badges

answered Jun 21, 2021 at 1:54

tmskipper86

462 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Open Excel file in SharePoint through VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related