I am trying to copy-paste from one Excel into another Excel programmatically from the Delphi code, the extded question and the source code is in Excel Copy-Paste from Delphi (OLE) with all the formatting?
It is possible to copy-paste the column-width and data&formatting in Excel applications following https://www.extendoffice.com/documents/excel/1867-excel-copy-column-width.html using 2 consecutive operations from the Excel applications: 1) Paste Special... - Other Paste Options - Column Widths
; 2) Paste Special... - Other Paste Options - All Using Source Theme
.
Excel specification https://docs.microsoft.com/en-us/office/vba/api/excel.range.pastespecial and https://docs.microsoft.com/en-us/office/vba/api/excel.xlpastetype defines, that the respective codes for those 2 operations are:
xlPasteColumnWidths 8 Copied column width is pasted.
xlPasteAllUsingSourceTheme 13 Everything will be pasted using the source theme.
So, the solution for my problem should be:
Sheet.Range['A1','CJ26'].PasteSpecial(8);
Sheet.Range['A1','CJ26'].PasteSpecial(13);
But that creates very strange excel - column widths are not copied, formatting is copied, but data are copied as the hyperlinks.
Excel_TLB shows that constants are correct indeed:
type
XlPasteType = TOleEnum;
const
xlPasteAllUsingSourceTheme = $0000000D; //13
xlPasteColumnWidths = $00000008; //8
xlPasteValidation = $00000006;
So, clearly, Delphi Excel OLE does not conform to the specification, i.e. something with Excel specification or with Excel libraries has gone bad! What to do?
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
See Question&Answers more detail:
os