Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Re: Adding pictures to Excel

by davies (Vicar)
on Jan 23, 2010 at 14:02 UTC ( #819186=note: print w/ replies, xml ) Need Help??


in reply to Adding pictures to Excel

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


Comment on Re: Adding pictures to Excel
Select or Download Code
Re^2: Adding pictures to Excel
by merrymonk (Friar) on Jan 23, 2010 at 14:42 UTC
    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^2: Adding pictures to Excel
by merrymonk (Friar) on Jan 23, 2010 at 15:01 UTC
    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 happens when you need to put something in {braces}. I have changed the code in the earlier node to include this, but I have commented in Re: coding for excel in perl on the impenetrability of the rules for using braces.

        Regards,

        John Davies

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://819186]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (10)
As of 2014-09-17 19:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (95 votes), past polls