使用Excel VBA在OneDrive上打开文件 [英] Open a file on OneDrive using Excel VBA

问题描述

我有一个Excel文件(文件#1)，我想要VBA代码在OneDrive上打开文件(文件#2).

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

许多人将使用文件#1，因此我希望它在后台打开文件#2.

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

我为文件#2创建了一个共享链接.有了这个链接，任何人都应该可以访问该文件.

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

如何通过文件1中的VBA代码打开文件2? 我希望它在Excel中而不是在线Excel中打开.

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

我使用了这段代码.

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

此行代码运行时，Excel将关闭，并打开一个窗口，显示Microsoft错误报告.

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

然后说

"Microsoft Excel出现问题，对于给您带来的不便，我们深表歉意.MicrosoftExcel将尝试恢复您的工作，但是最近的更改可能会丢失."

"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."

推荐答案

尝试以下操作:

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

上面的代码对我来说很好，但是也许您应该将OneDrive路径更改为类似这样的名称:"

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 :)

这篇关于使用Excel VBA在OneDrive上打开文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！