Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Re^3: Adding pictures to Excel

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

in reply to Re^2: Adding pictures to Excel
in thread Adding pictures to Excel

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.


John Davies

Comment on Re^3: Adding pictures to Excel
Select or Download Code

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (10)
As of 2015-12-01 15:25 GMT
Find Nodes?
    Voting Booth?

    My keyboard shows this many letters:

    Results (14 votes), past polls