title: Copy entire worksheet from one instance of Excel to another
body: <p>According to <a href="http://office.microsoft.com/en-001/excel-help/move-or-copy-a-worksheet-HP001217035.aspx#BM2" rel="nofollow noreferrer">this article</a>, 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. </p>
  
  <p>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 (<code>ThisWorkbook</code>) into workbook opened in the first instance (<code>Wb</code>). I'm using <a href="https://stackoverflow.com/a/3303016/2572082">ForEachLoop's solution</a> to get <code>Wb</code>:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>As you can guess, I first tried to use <code>Worksheet.Copy</code> method then <code>Range.Copy</code> method and they both don't work. And the direct range assignment copies only values and I need also formatting to be copied.</p>
  
  <p>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.</p>