title: Excel Copy-Paste from Delphi (OLE) with all the formatting?
body: <p>I have Excel file (old *.xls format, but that should be irrelevant here) with very complex formatting and data: differently merged cells, cells/ranges with custom backgrounds, fonts and so on. And I am trying programmatically copy/paste rectangular region from on file to other. I am using Delphi (2009, but that should be irrelevant as well), I am using OLE code and Excel_TLB. I have the following code: </p>
  
  <pre><code>procedure TMainForm.PrepareBtnClick(Sender: TObject);
  var OldFileName: string;
      OldApp: OleVariant;
      OldWorkbook: OleVariant;
      OldSheet: OleVariant;
  
      App: OleVariant;
      Workbook: OleVariant;
      Sheet: OleVariant;
  begin
    OldFileName:='D:\Work_Demo\ExcelCopyPasteRegion\out\Old_Excel_Test.xls';
  
    OldApp:=CreateOleObject('Excel.Application');
    try
      OldApp.Visible:=False;
      OldApp.Workbooks.Open(OldFileName);
      OldWorkbook:=OldApp.Workbooks[1];
      OldSheet:=OldWorkbook.WorkSheets[1];
      OldSheet.Range['A1','CJ26'].Copy;
    finally
      OldApp:=unassigned;
      OldSheet:=unassigned;
    end;
  
    App := CreateOleObject('Excel.Application');
    try
      App.Visible := False;
      App.Caption:='Test';
      App.Workbooks.Add(xlWBatWorkSheet);
      Workbook:=App.Workbooks[1];
      Sheet := Workbook.WorkSheets[1];
      Sheet.Name:='Test 123';
      //Sheet.Paste;
      //Sheet.PasteSpecial(Excel.XlPasteType.xlPasteFormats); //Does not work
      //Sheet.PasteSpecial(xlPasteFormats); //As picture
      //Sheet.PasteSpecial; //As picture
      //Sheet.PasteSpecial(xlPasteAll);
  
      //Sheet.Range['A1','CJ26'].PasteSpecial(xlPasteFormats); //Strange blue background
      //Sheet.Range['A1','CJ26'].Paste;
      Sheet.Range['A1','CJ26'].PasteSpecial(xlPasteAll, xlPasteSpecialOperationNone, True, False); //Cells are selected only, nothing happens
    finally
      if not VarIsEmpty(App) then begin
        App.Visible:=true;
      end;
      App:=unassigned;
      Sheet:=unassigned;
    end;
  end;
  </code></pre>
  
  <p>The problem is that my special past is always bad. Sometimes just picture is captured in pasted into new Excel file. Other times nothing happens and just the range is selected in the target Excel, sometimes all the data and some formatting is copied, but the formatting is very strange: gray background colors has been converted to blue colors and the column width/cell height has been lost. I have left some code that I have tried, but still I am searching for the code that can work.</p>
  
  <p>Is this all dependant on my Excelt_TLB? And what happens if I am generating Excel_TLB from the most up-to-date Excel version and I am including it in my project, but the program is used on the computer with the previous versions of Excel?</p>
  
  <p>I have also encountered the following erros while experimenting:</p>
  
  <pre><code>PasteSpecial method of Worksheet class failed.
  The RPC server is unavailable.
  </code></pre>
  
  <p><a href="https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.pastespecial" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.pastespecial</a> mentions that PasteSpecial on worksheet always pastes as picture, so, that function is not suitable for me. But the configuration structure for the Range.PasteSpecial <a href="https://docs.microsoft.com/en-us/office/vba/api/excel.xlpastetype" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/office/vba/api/excel.xlpastetype</a> is far more rich than I have in Excel_TLB. My TLB has notes:</p>
  
  <pre><code>// PASTLWTR : $Revision:   1.130.1.0.1.0.1.6  $
  // File generated on 13.09.2005 13:41:06 from Type Library described below.
  </code></pre>
  
  <p>Maybe it is a bit old? But I am afraid to generate new Excelt_TLB as I can loose compatibility with the legacy versions of Excel.</p>
  
  <p>Well, I don't need the most up-to-date Excel_TLB, because I can use Integer constants instead of named constants that are defined in the latest Excel_TLB only, so - I can call code:</p>
  
  <pre><code>Sheet.Range['A1','CJ26'].PasteSpecial(13);
  </code></pre>
  
  <p>for </p>
  
  <pre><code>Sheet.Range['A1','CJ26'].PasteSpecial(xlPasteAllUsingSourceTheme);
  </code></pre>
  
  <p>But it is still of no value - hyperlink to the original cells are pasted in the new Excel. So - this is not solution as well.</p>
  
  <p>I repeated this sequence of copy-paste manually and recorded VB macro - this macro is exactly the Delphi code which I am trying to run:</p>
  
  <pre><code>Sub Macro1()
      Range("A1").Select
      Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
          SkipBlanks:=False, Transpose:=False
      Range("A1").Select
      Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
          , SkipBlanks:=False, Transpose:=False
      Range("E10").Select
      Application.CutCopyMode = False
  End Sub
  </code></pre>