将整个工作表从一个Excel实例复制到另一个实例

According to this article, you cannot move or copy worksheet from one instance of Excel to another. Unfortunately, it's the only way I have to make my program function properly.

根据本文，您无法将工作表从一个Excel实例移动或复制到另一个实例。不幸的是，这是我必须使程序正常运行的唯一方法。

I have 2 instances of Excel: one is run by our ancient ERP system and another through OLE call. The macros running in the second should copy first worksheet from opened workbook (ThisWorkbook) into workbook opened in the first instance (Wb). I'm using ForEachLoop's solution to get Wb:

我有2个Excel实例：一个是由我们古老的ERP系统运行，另一个是通过OLE调用运行。在第二个中运行的宏应该将第一个工作表从打开的工作簿（ThisWorkbook）复制到第一个实例（Wb）中打开的工作簿中。我正在使用ForEachLoop的解决方案来获取Wb：

Public Function GetExcelObjectFromHwnd(ByVal hWnd As Long) As Boolean
...
    If AccessibleObjectFromWindow(hWnd, OBJID_NATIVEOM, iid, obj) = 0 Then 'S_OK
        Dim objApp As Excel.Application
        Set objApp = obj.Application
        Dim Wb As Workbook
        For Each Wb In objApp.Workbooks
            ProcessWorkbook Wb
        Next Wb

        fOk = True
    End If
...
End Function

Sub ProcessWorkbook(Wb as Worksheet)
...
    'This produces error because ThisWorkBook and Wb are opened in different instances of Excel:
    ThisWorkbook.Sheets(1).Copy , Wb.Sheets(1)
    'What I developed so far
    Wb.Sheets.Add , Wb.Sheets(1)
    'this doesn't work too:
    ThisWorkbook.Sheets(1).UsedRange.Copy Wb.Sheets(2).Range("A1")
    'and this works but doesn't copy formatting:
    With ThisWorkbook.Sheets(1).UsedRange
        Wb.Sheets(2).Range("A1").Resize(.Rows.Count, .Columns.Count) = .Value
    End With
    ' later I perform some operations with cells
...
End Sub

As you can guess, I first tried to use Worksheet.Copy method then Range.Copy method and they both don't work. And the direct range assignment copies only values and I need also formatting to be copied.

你可以猜到，我首先尝试使用Worksheet.Copy方法然后使用Range.Copy方法，它们都不起作用。并且直接范围分配仅复制值，我还需要格式化以进行复制。

So, apparently, solution which will copy formatting is appropriate, but I'd prefer direct copying if there is any way to do it. Also, please don't suggest to use clipboard, as it is always bad idea.

因此，显然，复制格式的解决方案是合适的，但如果有任何方法，我更喜欢直接复制。此外，请不要建议使用剪贴板，因为它总是坏主意。

1 个解决方案

#1

I suggest you SaveAs your workbook from Excel instance A to a temp file, and then open this temp file in Excel instance B in order to copy the sheet you need.

我建议您将工作簿从Excel实例A另存为临时文件，然后在Excel实例B中打开此临时文件以复制所需的工作表。