How to set properties of a picture I paste in Excel?

Question

I have pictures (TBitMap) in cells of the TAdvStringGrid, and I want to copy this table to an Excel file using OLEVariant. Below I will paste only a few lines of code to introduce you with 2 ways I can paste one picture 'in' a particular cell of the Excel file from the particular TStringGrid cell:

// 1st way

Clipboard.Assign(StringGrid1.GetBitmap(2, 2));
Worksheet.Range['a1','a1'].Select;
Worksheet.Paste;

// 2nd way

bmp := StringGrid1.GetBitmap(2, 2);
bmp.SaveToFile('test.bmp');
Worksheet.Range['a1','a1'].Select;
Worksheet.Pictures.Insert('test.bmp');

I wrote 'in' using quotes because, in the resulting Excel sheet, pasted image is not really attached to a cell I used in code, that is, if I change height/width of row/column the cell related to, the picture will not follow it and/or change it size accordingly.

I googled that there are properties for pictures in Excel that can associate and lock them to a cell if set as True (ticked in the checklist in "Format picture" menu):

move and size with cell
print object
locked.

Unfortunately, I couldn't find the way to access those properties using Delphi, only VBA examples. So if you know how to do that (even if a different ways of pasting or Excel document creation should be used), please, share, it will be highly appreciated.

Upd. 1. The VBA code I mentioned is:

Dim P As Object
Dim xlApp As Excel.Application
Dim WB As Workbook

Set xlApp = New Excel.Application

With xlApp
     .Visible = False
     .DisplayAlerts = False
End With

Set WB = xlApp.Workbooks.Open(FilePath, , True)

Set P = xlApp.Sheets(1).Pictures.Insert(PicPath)
With P
     With .ShapeRange
          .LockAspectRatio = msoFalse
          .Width = 375
          .Height = 260
     End With
     .Left = xlApp.Sheets(1).cells(y, x).Left
     .Top = xlApp.Sheets(1).cells(y, x).Top
     .Placement = 1
     .PrintObject = True
End With

WB.SaveAs FileName:= NewName, CreateBackup:=False 
WB.Close SaveChanges:=True

xlApp.DisplayAlerts = True
xlApp.Application.Quit

(taken from OP post here)

VBA and Delphi are very close when automating Excel. If you have the VBA code to do what you need, edit your question to add the VBA code and then ask how to translate it to Delphi. BTW: You'll understand that I will not do the search you've already done. — fpiette, Oct 16 '20 at 19:01
If you use a `copy` function after the `select`, you will be able to use `PasteSpecial ` and set properties using that. — JoshL, Oct 16 '20 at 20:54
@fpiette I updated the post and added that VBA code I'd like to translate to Delphi. ofc I should've done it myself. Thanks in advance. — endocringe, Oct 19 '20 at 09:24
@JoshL how exactly and when do I set properties? As I understood, PasteSpecial arguments cannot do what I need — endocringe, Oct 19 '20 at 11:13
@endocringe are the properties that you are trying to set just the size of the picture? — JoshL, Oct 19 '20 at 13:12
@JoshL not really. First of all I need to lock an image to a cell I paste it into (selected cell in code examples for picture insertion in OP post) so it moves and resizes according to the cell distortions. As I understood and mentioned in OP post, for all above mentioned in Excel, 3 following properties must be set True: move and size with cell, print object, locked. — endocringe, Oct 19 '20 at 13:47

score 1 · Accepted Answer · answered Oct 19 '20 at 16:25

I ported to Delphi the VBA code you showed:

uses Excell2000;

procedure TForm1.Button1Click(Sender: TObject);
var
    AWorkbook   : ExcelWorkbook;
    AWorkSheet  : OleVariant;
    ASpecOffset : OleVariant;
    APicture    : OleVariant;
    AShapeRange : OleVariant;
    PicPath     : String;
    FilePath    : String;
    NewName     : String;
const
    Lcid = 0;
begin
    FilePath := 'YourExcelFile.xls';
    NewName  := 'YourGeneratedExcelFile.xls';
    PicPath  := 'YourImage.jpg';

    Memo1.Clear;
    ExcelApplication1.Connect;
    ExcelApplication1.Visible[Lcid] := TRUE;
    try
        // Open() will trigger an EOleException if file not found or
        // other similar error.
        AWorkbook := ExcelApplication1.Workbooks.Open(
                             FilePath,
                             EmptyParam,  // UpdateLinks
                             EmptyParam,  // ReadOnly
                             EmptyParam,  // Format
                             EmptyParam,  // Password
                             EmptyParam,  // WriteResPassword
                             EmptyParam,  // IgnoreReadOnlyRecommended
                             EmptyParam,  // Origin
                             EmptyParam,  // Delimiter
                             EmptyParam,  // Editable
                             EmptyParam,  // Notify
                             EmptyParam,  // Converter
                             EmptyParam,  // AddToMru
                             Lcid);
    except
        on E: EOleException do begin
            Memo1.Lines.Add(E.Message);
            Exit;
        end;
    end;

    if ExcelApplication1.Workbooks.Count < 1 then begin
        Memo1.Lines.Add('No workbook found.');
        Exit;
    end;
    if ExcelApplication1.Worksheets.Count < 1 then begin
        Memo1.Lines.Add('No worksheet found.');
        Exit;
    end;

    // Get hand on first worksheet
    AWorkSheet                  := AWorkBook.WorkSheets[1];
    APicture                    := AWorkSheet.Pictures.Insert(PicPath);
    AShapeRange                 := APicture.ShapeRange;
    AShapeRange.LockaspectRatio := FALSE;
    AShapeRange.Width           := 375;
    AShapeRange.Height          := 260;
    APicture.Left               := AWorkSheet.Cells[4, 5].Left;
    APicture.Top                := AWorkSheet.Cells[4, 5].Top;
    APicture.Placement          := 1;
    APicture.PrintObject        := TRUE;

    AWorkBook.SaveAs(NewName,          // FileName
                     xlExcel7,         // FileFormat
                     EmptyParam,       // Password
                     EmptyParam,       // WriteResPassword
                     EmptyParam,       // ReadOnlyRecommended
                     TRUE,             // CreateBackup
                     xlNoChange,       // AccessMode
                     EmptyParam,       // xlUserResolution, // ConflictResolution
                     EmptyParam,       // AddToMru
                     EmptyParam,       // TextCodepage
                     EmptyParam,       // TextVisualLayout
                     Lcid);            // Local

    // Close the work book
    AWorkBook.Close(FALSE, EmptyParam, EmptyParam, Lcid);
    // If no other workbook still open, close Excel
    if ExcelApplication1.Workbooks.Count < 1 then
        ExcelApplication1.Quit;
    // Disconnect from Excel
    ExcelApplication1.Disconnect;
end;

To make code easier to read, I used intermediate variables you can suppress or use "with" clause (Not recommended).

Thank you so much! I tried to do this manually directly on an Excel workbook and I guess it's the best Excel is capable of speaking of pictures insertion — endocringe, Oct 20 '20 at 14:45

How to set properties of a picture I paste in Excel?

1 Answers1