Open a file on OneDrive using Excel VBA

Question

I have an Excel file (File #1) in which I would like the VBA code to open a file on my OneDrive (File #2).

Many people will use File #1, so I want it to open File #2 in the background.

I have created a shared link for File #2. With this link anyone should be able to access the file.

Link: https://1drv.ms/x/s!AmdCer05hKV4geAnbDaOSX9UrTfLHQ?e=5Pm7dL

How do I open this File #2 via VBA code in File #1? I want it to open in Excel, not Excel on-line.

I used this code.

Set wb = Workbooks.Open("https://1drv.ms/x/s!AmdCer05hKV4geAnbDaOSX9UrTfLHQ?e=5Pm7dL")

When this line of code runs, Excel shuts down and a window opens stating Microsoft Error Reporting.

It then says

"There was a problem with Microsoft Excel and we apologize for the inconvenience. Microsoft Excel will attempt to recover your work, but recent changes might be lost."

`Set wb = workbooks.Open( "https://1drv.ms/x/s!AmdCer05hKV4geAnbDaOSX9UrTfLHQ?e=5Pm7dL")` assuming everyone has the appropriate permissions — Tim Williams, Oct 24 '19 at 04:30
Welcome to SO! When you place a question try to add a minimum content: input sample, expected output sample, what did you try, research and where are you stuck. What did you try? Show it. — David García Bodego, Oct 24 '19 at 05:01
Yes. I tried it. I added the result back to the main body above. It caused the file to close and gave me an error. — Mark Hamilton, Oct 25 '19 at 12:34

score 2 · Answer 1 · answered Nov 26 '19 at 13:01

Try the following:

Sub open_excel_from_one_drive()

sfilename = "https://1drv.ms/x/s!AmdCer05hKV4geAnbDaOSX9UrTfLHQ?e=5Pm7dL"
Set xl = CreateObject("Excel.Sheet")
Set xlsheet = xl.Application.Workbooks.Open(Filename:=sfilename, ReadOnly:=True)

End sub

The above code works fine for me, but maybe you should change your OneDrive path to be something like that: "https://yourcorp.sharepoint.com/sites/yoursite/Shared%20Documents/1stfolder/myfile.xlsx"

I hope it helps :)

score 0 · Answer 2 · answered Nov 03 '22 at 17:20

If I open it from VBA using the 1drv.ms/... link that it gives to me when I "Share" it, it asks me for a Microsoft login/password that is not my usual Microsoft login. I never found the right password and it's not recoverable.

I solved by using the https://d.docs.live.net/... URL I obtain with a ?ThisWorkbook.fullname in the VBA window. This works without asking a login/password, likely using the account used by the Excel application.

score -1 · Answer 3 · answered Sep 01 '21 at 18:02

-1

I ended up using the below since no other answer on here worked.

ActiveWorkbook.FollowHyperlink Address:="https://1drv.ms/x/s!AmdCer05hKV4geAnbDaOSX9UrTfLHQ?e=5Pm7dL"

answered Sep 01 '21 at 18:02

W-hit

353
3
14

score -1 · Answer 4 · edited Jan 17 '22 at 13:42

-1

This will let you copy a clear path to the file:

Go to the sharepoint site where it is saved. Example: https://dieboldnixdorf-my.sharepoint.com/
Locate the file
Select the 3 dots next to the file
Select Details
Scroll down to the "Path" section
Select the copy option

edited Jan 17 '22 at 13:42

answered Jan 14 '22 at 14:00

StefanieB

1

Open a file on OneDrive using Excel VBA

4 Answers4

Linked