Re: Adding pictures to Excel

[davies]

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

[merrymonk]

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?

[merrymonk]

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;

[davies]

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

[merrymonk]

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)?

[davies]

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