将整个工作表从一个Excel实例复制到另一个 [英] Copy entire worksheet from one instance of Excel to another

问题描述

根据本文，您无法将工作表从一个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.

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

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:

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

您可以猜到，我首先尝试使用Worksheet.Copy方法，然后使用Range.Copy方法，但它们都无法正常工作.直接范围分配仅复制值，我还需要复制格式.

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.

因此，显然，可以使用复制格式的解决方案是合适的，但是如果有任何方法，我宁愿直接复制.另外，请不要建议使用剪贴板，因为这总是一个坏主意.

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.

推荐答案

我建议您SaveAs将工作簿从Excel实例A复制到临时文件，然后在Excel实例B中打开此临时文件以复制工作表你需要.

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实例复制到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！