Office 365 工作簿.从 SharePoint Online 打开打开空白文件 [英] Office 365 Workbooks.open from SharePoint Online opens blank file

问题描述

我们公司正在半年更新频道上使用 Office 365 Pro Plus.最近我们从1708版升级到了1803版.

我们使用了一些自动化宏，而不是从我们的 SharePoint 在线租户打开文件.自更新以来，调用 Workbooks.open("

本地文档上的 Workbooks.open() 仍然像以前一样工作，只有从 SP Online 打开文件有这个问题.

我尝试在计算机上切换到每月频道，但问题仍然存在.使用链接手动打开文件有效.

这是我们使用的示例代码:

Sub Transfert_SST_Copy()Dim Tbl 作为 ListObjectDim NewRow 作为 ListRow将数据变暗为 ListRowDim Wb 作为工作簿设置 Wb = Workbooks.Open(Filename:="https://xxxxx.sharepoint.com/:x:/r/sites/XXX/AMTB%20RFQ%20costing/00%20General/Overview%20RFQs/Cost%20computations%20simple%20overview/2018%20AMTB_Cost_computations_overview_simple.xlsm?d=w8929b5112ed7496bb25d82b0bfc472c5&csf=1&e=PADrRt", ReadOnly:=False)' 这里 Excel 会打开一个空白"工作簿，因此之后的所有内容都会出错Set Tbl = Range("CostCalcOverview").ListObject设置 NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)NewRow.Range.Offset(0, 1).Resize(1, ThisWorkbook.Worksheets("Transfert").Range("A275:AW275").Count).Value = ThisWorkbook.Worksheets("Transfert").Range("A275:AW275").值结束子

解决方案

我遇到了同样的问题 - 它让我觉得很简单，因为它看起来很简单！问题在于您的文件名链接.

 Set Wb = Workbooks.Open(Filename:="https://xxxxx.sharepoint.com/:x:/r/sites/XXX/AMTB%20RFQ%20costing/00%20General/Overview%20RFQs/Cost%20computations%20simple%20overview/2018%20AMTB_Cost_computations_overview_simple.xlsm?d=w8929b5112ed7496bb25d82b0bfc472c5&csf=1&e=PADrly:FalseOnly:

当您单击共享"或复制链接"时，您使用的文件名链接直接来自 SharePoint，但为我解决此问题的是实际在桌面 Excel 中打开 SharePoint 文件.然后单击文件.然后，您将看到显示文件名的信息"页面 - 如果您直接查看文件名下方(上面写着保护工作簿")，则看起来像面包屑链接(来自 SharePoint 的文件夹名称由 >> 分隔)-单击该部分，然后单击将路径复制到剪贴板"

用这个替换你代码中的文件名链接，它会起作用！

Our company is using Office 365 Pro Plus on Semi-Annual update channel. Lately we've been updated from version 1708 to 1803.

We are using some automation macros than open files from our SharePoint online tenant. Since the update, a call of Workbooks.open("https://xxxxxx.sharepoint.com/sites/.../xxx.xlsx") now prompts for an O365 user and password every time, and don't use the one used as Excel account.

It opens a "blank" workbook (see screenshot) :

Workbooks.open() on local documents is still working as before, only opening files from SP Online has this problem.

I tried to switch to monthly channel on a computer, but the problem persists. Opening the file manually using the link is working.

Here is a sample code we're using :

Sub Transfert_SST_Copy()

Dim Tbl As ListObject
Dim NewRow As ListRow
Dim Data As ListRow
Dim Wb As Workbook

Set Wb = Workbooks.Open(Filename:="https://xxxxx.sharepoint.com/:x:/r/sites/XXX/AMTB%20RFQ%20costing/00%20General/Overview%20RFQs/Cost%20computations%20simple%20overview/2018%20AMTB_Cost_computations_overview_simple.xlsm?d=w8929b5112ed7496bb25d82b0bfc472c5&csf=1&e=PADrRt", ReadOnly:=False)
' Here Excel opens a "Blank" Workbook, so everything after that is giving an error
Set Tbl = Range("CostCalcOverview").ListObject
Set NewRow = Tbl.ListRows.Add(AlwaysInsert:=True)

NewRow.Range.Offset(0, 1).Resize(1, ThisWorkbook.Worksheets("Transfert").Range("A275:AW275").Count).Value = ThisWorkbook.Worksheets("Transfert").Range("A275:AW275").Value

End Sub

解决方案

Hi I had the same issue - and it was getting to me as it seemed such an easy thing! The problem is with your filename link.

 Set Wb = Workbooks.Open(Filename:="https://xxxxx.sharepoint.com/:x:/r/sites/XXX/AMTB%20RFQ%20costing/00%20General/Overview%20RFQs/Cost%20computations%20simple%20overview/2018%20AMTB_Cost_computations_overview_simple.xlsm?d=w8929b5112ed7496bb25d82b0bfc472c5&csf=1&e=PADrRt", ReadOnly:=False)

The filename link you used is directly from SharePoint when you click "Share" or "Copy link", but what resolved this for me is by actually opening the SharePoint file in Desktop Excel. Then click File. You are then shown the "Info" page which shows the File Name - if you look directly below the filename (above where it says "Protect Workbook") there looks to be like breadcrumb links (folder names from SharePoint separated by >>) - click that section and click "Copy path to clipboard"

Replace your Filename link in your code with this and it will work!

这篇关于Office 365 工作簿.从 SharePoint Online 打开打开空白文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！