http://qs321.pair.com?node_id=819178

merrymonk has asked for the wisdom of the Perl Monks concerning the following question:

I found a file of Perl one liners for writing to Excel files.
To add images it gives the following Perl
#___ INSERT PICTURE $sheet -> Pictures -> Insert("picture_name"); # Insert +in upper-left corner $excel -> ActiveSheet -> Pictures -> Insert("picture_name"); # Insert +in active cell #___ ACTIVATE CELL $sheet -> Range("A2") -> Activate;
I tried to use this to add pictures as specific cells. The Perl code is below.
In this code I attempted to add pictures at two specific cells, A1 and F10.
I hoped that the top left hand corner of the picture would be in the defined cell. Two pictures were added but
unfortunately they were added on top of each other and the cell was not either of the ones I specified.
Can any Monk enlighten me as to what changes I should make to get the pictures where I want them?
use OLE; use Win32::OLE::Const "Microsoft Excel"; use strict "vars"; my ($excel, $workbook, $sheet); my ($cell_id, $image_file_full); #___ DEFINE EXCEL $excel = CreateObject OLE "Excel.Application"; #___ MAKE EXCEL VISIBLE $excel -> {Visible} = 1; #___ ADD NEW WORKBOOK $workbook = $excel -> Workbooks -> Add; $sheet = $workbook -> Worksheets("Sheet1"); $sheet -> Activate; $cell_id = "A1"; $sheet -> Range($cell_id) -> Activate; $image_file_full = "full directory path and image name here”; $excel -> ActiveSheet -> Pictures -> Insert($image_file_full); # Inser +t in active cell $cell_id = "F10"; $excel -> ActiveSheet -> Pictures -> Insert($image_file_full);

Replies are listed 'Best First'.
Re: Adding pictures to Excel
by davies (Prior) on Jan 23, 2010 at 14:02 UTC
    Warning - untested code follows! :-)

    My usual approach when trying to find out how to automate something I am unfamiliar with in Excel is to record a macro, and I'm afraid that what I did confirmed my expectations. A picture is not related to a cell, but to a sheet. Therefore, all your references to cells, and your attempt to position the pictures relative to cells, are doomed to failure. You will probably have realised that your line $excel -> ActiveSheet -> Pictures -> Insert($image_file_full); # Insert in active cell makes no reference to the active cell, only the active sheet. However, I believe, based on other peoples' files including pictures that I have worked on, that inserting and deleting rows and columns will move and resize pictures.

    Anyway, I recorded a macro and put two pictures into a sheet. The first thing that happened was, as you described, that the pictures went one on top of the other. So I dragged one of them down. The resulting VBA macro was:
    ActiveSheet.Pictures.Insert( _ "C:\Documents and Settings\All Users\Documents\My Pictures\Sam +ple Pictures\Blue hills.jpg" _ ).Select ActiveSheet.Pictures.Insert( _ "C:\Documents and Settings\All Users\Documents\My Pictures\Sam +ple Pictures\Sunset.jpg" _ ).Select Selection.ShapeRange.IncrementTop 290.25
    Although VBA litters recorded macros with "Activate" and "Select", it's usually bad practice to let these leak into production code, as they slow the system down without doing anything useful. It's OK to use them to get the user where you want him when you finish, and there are certain situations where they can't be avoided. But if I were trying to put two pictures into an Excel sheet, my VBA would look like the following untested code:
    Dim picCurrent As Picture Dim shtPics As Worksheet Set shtPics = ThisWorkbook.Sheets(1) Const ksPic1 As String = "C:\Documents and Settings\All Users\Document +s\My Pictures\Sample Pictures\Blue hills.jpg" Const ksPic2 As String = "C:\Documents and Settings\All Users\Document +s\My Pictures\Sample Pictures\Sunset.jpg" Set picCurrent = shtPics.Pictures.Insert(ksPic1) Set picCurrent = shtPics.Pictures.Insert(ksPic2) picCurrent.Top = 130 picCurrent.Left = 280
    From this, my first pass at Perl would be:
    use strict; use warnings; use diagnostics; use Win32::OLE; my $xl = Win32::OLE->new('Excel.Application'); $xl->{Visible} = 1; my $wb = $xl->ActiveWorkbook; my $shtPics = $wb->Sheets(1); my $picCurrent = $shtPics->Pictures->Insert(first pic); $picCurrent = $shtPics->Pictures->Insert(second pic); $picCurrent->Top = 130; $picCurrent->Left = 280;
    ... and then I would start debugging.

    HTH,

    John Davies
      I altered my example to try and move the pictures after they had been inserted but nothing happened.
      Therefore the two pictures were added on top of each other.
      Have you any idea what I should do next?
        Soory, I should have said that I got the following error
        Can't modify non-lvalue subroutine call at ....
        for the line
        $picCurrent->Top = 130;
      That looks very logical so I will try.
      Just one thing though. I was hoping to be able to use referecnes to cells.
      The code you proposed seems to work in co-ordinates.
      Is there someway of getting the co-ordinates of a cell (or something similar)?
        The following VBA is at least pretty close to working. The picture appears slightly below the top of the cell, but that could be due to a narrow top boundary in the picture.
        Dim picCurrent As Picture Dim shtPics As Worksheet Set shtPics = ThisWorkbook.Sheets(1) Const ksPic1 As String = "C:\Documents and Settings\All Users\Document +s\My Pictures\Sample Pictures\Blue hills.jpg" Const ksPic2 As String = "C:\Documents and Settings\All Users\Document +s\My Pictures\Sample Pictures\Sunset.jpg" Set picCurrent = shtPics.Pictures.Insert(ksPic1) Set picCurrent = shtPics.Pictures.Insert(ksPic2) Dim nHPos As Long Dim nVPos As Long Dim rTarget As Range Set rTarget = ActiveSheet.Range("F10") Dim nRow As Long Dim nCol As Long nHPos = 0 nVPos = 0 For nRow = 1 To rTarget.Row - 1 nVPos = nVPos + ActiveSheet.Cells(nRow, 1).Height Next nRow For nCol = 1 To rTarget.Column - 1 nHPos = nHPos + ActiveSheet.Cells(1, nCol).Width Next nCol picCurrent.Top = nVPos picCurrent.Left = nHPos
        That would make the Perl something like:
        use strict; use warnings; use diagnostics; use Win32::OLE; my $xl; $xl = Win32::OLE->new('Excel.Application'); $xl->{Visible} = 1; my $wb = $xl->Workbooks->Add; my $shtPics = $wb->Sheets("Sheet1"); my $picCurrent = $shtPics->Pictures->Insert("C:\\Documents and Setting +s\\All Users\\Documents\\My Pictures\\Sample Pictures\\Blue hills.jpg +"); my $nHPos = 0; my $nVPos = 0; my $rTarget = $shtPics->Range("F10"); for (1..$rTarget->Row - 1) { $nVPos += $shtPics->Cells($_, 1)->Height; } for (1..$rTarget->Column - 1) { $nHPos += $shtPics->Cells(1, $_)->Width; } $picCurrent = $shtPics->Pictures->Insert("C:\\Documents and Settings\\ +All Users\\Documents\\My Pictures\\Sample Pictures\\Sunset.jpg"); $picCurrent->{Top} = $nVPos; $picCurrent->{Left} = $nHPos;
        Again, the Perl is untested.Update: tested, debugged & replaced the code.

        Regards,

        John Davies
Re: Adding pictures to Excel
by Albannach (Monsignor) on Jan 23, 2010 at 16:04 UTC
    You can add a picture so that the top-left of the image is in the top left of the active cell. You were actually very close in your attempt. I've tested that the following works:
    use strict; use warnings; use OLE; use Win32::OLE::Const "Microsoft Excel"; my $excel = Win32::OLE->new('Excel.Application'); $excel->{Visible} = 1; my $workbook = $excel->Workbooks->Add; my $sheet = $workbook->Sheets(1); my $path = 'c:/images/'; $sheet->Range('a1')->Activate; $sheet->Pictures->Insert($path.'Img1.jpg'); # top-left of pic in activ +e cell $sheet->Range('f10')->Activate; $sheet->Pictures->Insert($path.'Img2.jpg'); # top-left of pic in activ +e cell

    --
    I'd like to be able to assign to an luser

      In my experience, it's very rare to need to use the active cell for anything. But you're quite right, setting the active cell changes the import point. I, as Psmith would say, confused the unusual with the impossible. It's still ugly - I want my users to run my macros and end up back where they started. But I have Gargled extensively and found no way of putting a picture over a cell directly except by using the active cell. However, I have found an improvement on my previous code. Rather than the loops to work out the top and left of the target cell, the following works:
      $nVPos = $rTarget->Top; $nHPos = $rTarget->Left;
      And these can be refactored into the assignments so that the lines become something like:
      $picCurrent->{Top} = $rTarget->{Top}; $picCurrent->{Left} = $rTarget->{Left};
      Anchoring in this way means that inserting or deleting rows and columns moves the picture, so I think we now get the best of all worlds.

      Thanks, all - I've learned something new about the tool that pays my bills!

      John Davies
        Of course that's a good point that the starting location in the sheet is lost. I didn't consider that because if this is being done just to create reports in Excel (my normal use) then it really shouldn't matter as the user has never been in the sheet to begin with, but in the case of an existing sheet, it is an issue. I do like your idea of adjusting location after insertion, it seems neater than jumping around on the sheet and would only be improved if Excel would allow one to specify the insertion point to start with.

        I tested a bit and your method appears to have the same results as inserting in the active cell too, with respect to effects of row and column insertion/resizing shifting the cell later. I wondered if your method would associate the image with an absolute sheet location (which might even be handy in some cases), but it appears to link to the cell location, so all in all I think you have the winner!

        --
        I'd like to be able to assign to an luser

        Even better!! Great.
        I have tested more with this and confirmed that the pictures are placed where I wanted them to be.
      Many thanks for this contribution.
      Something odd has happended. I copied your Perl and inserted my path to the picture I wanted to use.
      On my system (Excel 2007 on Windows XP) the two pictures ended up on top of each other (and not near either of the target cells).
        That is odd. I tested both methods from this thread on my Win XP/Excel 2002 machine and they both work as expected. The only difference is that with davies' code above both images are inserted at the same point and you can see the second picture location change (but that won't be an issue when Excel visibility is turned off). With my version the images are inserted in the specified (different) places initially. Assuming your cell references in the code are not the same, I can't think of an explanation right now.

        --
        I'd like to be able to assign to an luser