将工作表从Sharepoint上关闭的工作簿复制到当前工作簿的VBA代码

Question

我正在尝试制作一个宏，从位于Sharepoint上的另一个工作簿(wbReport)复制工作表(wsReport)到宏所在的工作簿(wbMain)，将复制的工作表放在名为"main"的工作表之后;在wbMain。wsReport是wbReport中唯一的工作表。这应该在没有明显"打开"wbReport的情况下完成-我通过将ScreenUpdating设置为False来做到这一点。

相关代码如下:

Application.ScreenUpdating = False
Dim wbReport as Workbook
Dim wsReport as Worksheet
Dim wsMain as Worksheet
Dim ReportPath as String
Dim NewWindow as New Excel.Application
Set wsMain = ThisWorkbook.Worksheets("Main")
ReportPath = "https://company.sharepoint.com/FilePath"
Set wbReport = NewWindow.Workbooks.Open(ReportPath)
Set wsReport = wbReport.Worksheets(1)
wsReport.Copy After:=wsMain

直到最后一行。路径是正确的，如果我使ScreenUpdating = True，它会在视觉上打开正确的文件。如果我分步执行代码，我可以执行

Debug.Print wsReport.Range("A1")

，它从wsReport的单元格A1返回值，所以我知道这是工作的。但是只要它运行最后一行代码(实际要复制的行)，我就会得到运行时错误'1004':No such interface supported '。* *

我不知道为什么会出现这个错误。我找不到很多关于这个错误的信息，因为它涉及到复制数据，但似乎其他有这个问题的人没有正确地参考其他文件中的工作表，他们应该(例如)。"wsReport.Range(范围("A1" .Offset……"当它应该是"wsReport.Range(wsReport.Range("A1"). offset…)我想我已经通过明确定义所有相关的工作簿和工作表来避免这种情况(VBA认为它在什么工作簿中并不重要，wsReport应该总是指'https://company.sharepoint.com/FilePath'.Worksheets(1 ')， wsMain应该总是指' main ';)

我可以得到类似的工作，如果不是复制整个工作表，我复制单元格值(wsReport.Cells.Copy…xlpastevalvalues)到wbMain中的新工作表。但是，我需要wsReport中的所有内容，包括格式、注释等，所以我不能只是复制和粘贴值。当我尝试使用xlPasteAll时，它会做一件奇怪的事情，并在wsReport中粘贴透明的.png数据而不是实际数据-这显然不起作用。我想这也可以工作的解决方案，但只是复制整个表似乎更容易工作。

一般来说，我能找到的任何关于如何做到这一点的指导似乎都只是说"使用工作表"。正确复制方法，"据我所知，我确实是。也许我只是忽略了一些非常明显的东西。我在这里展示的代码可能并不完美(我匿名了它，可能遗漏了一些东西)，但这是宏中的前几行代码，因此在此之前没有发生任何可能破坏某些东西的事情。

Answer 1

我想明白了——我从另一个答案中得到了从Sharepoint打开文件的方法，一旦我确定它是有效的，我就没有认真地去看它。感谢BigBen找到一些显示不能在实例之间.Copy的东西还有蒂姆·威廉姆斯(Tim Williams)，因为他给了我没有必要的暗示。不需要打开一个新的Excel实例。下面的代码完成了我想要它做的事情:

Application.ScreenUpdating = False
Dim wbReport as Workbook
Dim wsReport as Worksheet
Dim wsMain as Worksheet
Dim ReportPath as String
Set wsMain = ThisWorkbook.Worksheets("Main")
ReportPath = "https://company.sharepoint.com/FilePath"
Set wbReport = Workbooks.Open(ReportPath)
Set wsReport = wbReport.Worksheets(1)
wsReport.Copy After:=wsMain
wbReport.Close

你可以直接使用路径打开文件，如果你不想让最终用户看到它打开，只需要确保在复制后关闭它(屏幕更新关闭，它永远不会可视化地打开一个窗口，除非你调试并在打开和关闭之间停止它)