2

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.

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

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.

Community
  • 1
  • 1
Danatela
  • 349
  • 8
  • 28

1 Answers1

2

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.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
  • Thanks for the brilliant idea! Actually, I used `If Wb.Application.hWnd <> Application.hWnd Then : Dim Fn As String : Fn = Wb.FullName : Wb.Close False : Set Wb = Workbooks.Open(Fn) : End If`. Those files were not changed at the moment macro executes. – Danatela Jun 06 '14 at 09:10