-4

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
TomR
  • 2,107
  • 5
  • 27
  • 48
  • 1
    Notice that there is no such thing as "Delphi Excel OLE". It's not like Delphi is shipped with a huge Pascal library for manipulating Excel -- you are talking directly to the Excel automation server (if I remember the terminology correctly). – Andreas Rejbrand Sep 11 '19 at 10:45
  • Which Excel version, and which Excel TLB interface unit are you using? – MartynA Sep 11 '19 at 12:06
  • 2
    There are no specifications for Delphi to conform with here, as it's just directly automating Excel through Excel's own automation objects. Delphi's COM support for Office is simply a wrapper around the underlying Office objects supplied by MS. It makes no changes to how they function. Read the source yourself. If the constants are defined correctly (as you indicate), and they're passed directly via COM to the automation objects (as you can see in the source, they are), then the issue is not a lack of Delphi conformity to any specification. Where's a [mcve] to demonstrate this issue? – Ken White Sep 11 '19 at 12:54
  • Well, if someone would want to investigate this problem, then question and linked question contains all the code and test Excel is just 4 cells and custom column width, so, no problem. If just one wants to speak out then it is hopeless. – TomR Sep 12 '19 at 04:45
  • 2
    In the other question you have been asked to try to repeat the operation manually. If you repeat **exactly** what your code does, you will quickly learn what is going on. The most important part is that the code is starting a second instance of Excel. – BrakNicku Sep 12 '19 at 05:41
  • I repated the actions manually and recorded a macro and I pasted that macro in both questions. VB code is exactly the one I am trying to run from Delphi. I also checked to create/open both Excel beforehand and only then copy from one Excel and paste into other Excel manually. No changes to the better direction. – TomR Sep 12 '19 at 06:01
  • 1
    No, what you have pasted is the operation recorded in **the same instance**. You have to do it in **two instances** - you will notice that there is no option to paste special as you would like. – BrakNicku Sep 12 '19 at 06:10
  • What I am doing manually: I open SourceExcelFile, I open NewExcelFile, I copy range from SourceExcelFile, I go the the opened NewExcelFile, start MacroWriting and with 2 Special Paste operations and I Paste range and then stop MacroRecroding. Well, I have "Run-time error '1004' PasteSpecial method of Range class failed" when I am trying to rerun recorded Macro. Uhh. – TomR Sep 12 '19 at 06:18
  • 1
    You have to start with the most important part - start two separate instances of Excel - you can easily find how to do it. This is not straightforward from user interface, but that is what your code is doing. – BrakNicku Sep 12 '19 at 06:22
  • Thanx @BrakNicku, I tried the Delphi code with one Excel.Application instance and that completely solved my problem. I suggest that you write short suggestion (about use of single Delphi OLE Excel.Application instance) as the answer to the question and then I will upvote and accept it as answer. Thanks. – TomR Sep 12 '19 at 06:31

1 Answers1

0

Two separate calls of CreateOleObject('Excel.Application'); in the Delphi code were made and that is why the paste did not work programmatically as expected. I copy/paste is called for the separate workbooks of the same instance of the Excel.Application, then PastSpecial (with codes 8 and 13) works as expected. That solves the issue.

TomR
  • 2,107
  • 5
  • 27
  • 48