excel - Open SharePoint file into desktop version of Excel

Question

I have the following code that has worked perfectly for many years:

palName = "S:\STAGE GATE\New Stuff 2021.xlsm"

(value is typically taken from a cell rather than hard coded)

Workbooks.Open Filename:=palName, UpdateLinks:=False

However, recently some of the locations for the files are from sharepoint so the palName would be as follows:

palName = "https://workwork.sharepoint.com/:x:/r/sites/newStuff/New%20Stuff%202021.xlsm?d=xyz&csf=1&web=1&e=123"

The usual Workbooks.Open does not work for these files. I would like to open the file in the desktop version of excel so I can copy data out of it. Is a new method required I can I adapt the Workbooks.Open accordingly to meet my needs.

Many thanks in advance, Alan.

标签： excelvba

Answer 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