Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Re^2: Adding pictures to Excel

by merrymonk (Friar)
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)?


Comment on Re^2: Adding pictures to Excel
Re^3: Adding pictures to Excel
by davies (Vicar) 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.

    Regards,

    John Davies

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (15)
As of 2015-07-02 13:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (38 votes), past polls