1

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: 

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;

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.

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?

I have also encountered the following erros while experimenting:

PasteSpecial method of Worksheet class failed.
The RPC server is unavailable.

https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.pastespecial 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 https://docs.microsoft.com/en-us/office/vba/api/excel.xlpastetype is far more rich than I have in Excel_TLB. My TLB has notes:

// PASTLWTR : $Revision:   1.130.1.0.1.0.1.6  $
// File generated on 13.09.2005 13:41:06 from Type Library described below.

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.

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:

Sheet.Range['A1','CJ26'].PasteSpecial(13);

for 

Sheet.Range['A1','CJ26'].PasteSpecial(xlPasteAllUsingSourceTheme);

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.

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:

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
TomR
  • 2,107
  • 5
  • 27
  • 48
  • I guess you tried with `Sheet.PasteSpecial(xlPasteAll);`, didn't you? What was the outcome? If you do the copying manually in Excel, what is the outcome? – Tom Brunberg Sep 11 '19 at 10:31
  • 1
    Apart from some `xl` constants, you don't use interfacaces from Excel type library in your snippet. You use late binding via `OleVariant`, so TLB version is irrelevant I'd say. You better provide proper [minimal, reproducible example](https://stackoverflow.com/help/minimal-reproducible-example), because we don't have any *Old_Excel_Test.xls* to test on our own. – Peter Wolf Sep 11 '19 at 10:33
  • I narrowed down the question to the nonconformenace of the Delphi Excel OLE to the specification https://stackoverflow.com/questions/57887377/why-delphi-excel-ole-functions-does-not-correspond-to-the-specificiation – TomR Sep 11 '19 at 10:44
  • xlPasteAll (both for Sheet.PasteSpecial and for Range.PasteSpecial) gives picture in the target Excel, clearly, it is not what I need. – TomR Sep 11 '19 at 10:45
  • Also clearly, you did not answer my other question so I repeat it: If you do the copying manually in Excel, what is the outcome? Then, as @PeterWolf already asked too, we need an Excel test file. – Tom Brunberg Sep 11 '19 at 11:05
  • In my other question https://stackoverflow.com/questions/57887377/why-delphi-excel-ole-functions-does-not-correspond-to-the-specificiation I described the manual copy-past with two paste operation and suggested the solution and showed that solution does not conform to the Excel specification provided by the Microsoft. I am prepareing test Excel, though I don't know how I will be able to add it to my question. – TomR Sep 11 '19 at 11:08

0 Answers0