Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

Re^2: Adding pictures to Excel

by merrymonk (Hermit)
on Jan 23, 2010 at 14:42 UTC ( #819189=note: print w/replies, xml ) Need Help??

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

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

Replies are listed 'Best First'.
Re^3: Adding pictures to Excel
by davies (Parson) on Jan 23, 2010 at 15:11 UTC
    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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://819189]
[Eily]: that's one long sheband
[choroba]: Already considered, but I don't understand why they replied with a shell script to "shell script to Perl"?

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (6)
As of 2018-05-25 08:20 GMT
Find Nodes?
    Voting Booth?