Quote:
Originally Posted by Dave Peterson
One more way...
After you've pasted the picture, it's the picture with the highest index.
dim myPict as picture
with activesheet
.paste
set mypict = .pictures(.pictures.count)
end with
mypict.name = "SamplePicture"
Leitey wrote:
I want to use VBA (and maybe some variation of the ActiveSheet.Paste
command?) to paste the image on the clipboard into Excel under a certain name
i.e. "samplepicture". I have tried "ActiveSheet.Paste.samplepicture" and
"ActiveSheet.Shapes("samplepicture").Paste" but can't seem to find a good
syntax.
--
Dave Peterson
|
About highest index and other items
To move image from one sheet to another through clipboard standard methods Copy-Paste are used. For Paste method you must define the range where the image is to be pasted, for example (you may skip Destination parameter):
Worksheets("Sheet1").Range("C1:C5").Copy
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("D1:D5")
A picture is inserted in the specified region, but some peculiarities exist:
• for Office 2003 pasted image is not binded exactly to upper-left corner of the range; if you define an alone cell the image may get the position more left and lower, even may get the adjacent cell; so you must execute realign procedure using Top and Left properties (see below);
• for Office 2003 pasted picture IS NOR selected, so special procedure must be done to identify the image in Shapes collection;
• for Office 2007 image is selected and binded to upper-left corner of the specified range, so Selection property can be used to change the image property in the collection (Name for example);
• pasted image index in Shapes collection becomes the upmost BUT AMONG THE PICTURES set (Type=msoPicture); in Office 2003 Shapes are groupped so that first is the block of controls (Lstbox, Combobox, etc.) and images block is latter, so pasted image index is in fact the last in all collection; for Office 2007 images block turns out to be BEFORE the block of controls, therefore you should search the index of the last pasted image between the elements of IMAGE BLOCK (see example below);
• to deselect pasted image (not to delete it by accident) you should move focus to any cell/ for example as Range("A1").Select.
Hence, to write a universal program that works correctly either in Office 2003, or Office 2007 environment you should:
- first, use special procedure to find out the pasted image (the reference, or index, of it in Shapes collection);
- second, make image alignment to upper-left corner of the range where the image is pasted;
- third, move focus to another cell.
Below is the Function that defines the index of last pasted image in the Shapes collection:
Function GetIndexPastedPicture() As Integer
' Pasted picture has the upmost index among the PICTURE block
' But it is not necessarily the last inde[ in whole collection
' set reference to target sheet with pasted image
Set ThisDBSheet = Workbooks("BookName.xls").Worksheets("SheetName")
Dim sh As Shape, picIdx As Integer
picIdx = 0 ' initial value of index in Shapes collection, starts from 1
For Each sh In ThisDBSheet.Shapes
If sh.Type = msoPicture Then ' image found
picIdx = sh.ZOrderPosition ' image index
End If
Next
' after For loop, picIdx - is the last index in PICTURE block
GetIndexPastedPicture = picIdx
End Function
Then (assuming that Clipboard already has the proper image) the procedure to paste image looks like following:
Sub InsPicFromClipbrd(sInsCell As String, sPicName As String)
' Image is pasted to cell with name sInsCell,
' it is aligned to upper-left corner of the cell,
' pasted image gets name sPicName in Shapes collection
' set reference to target sheet with pasted image
Set ThisDBSheet = Workbooks("BookName.xls").Worksheets("SheetName")
ThisDBSheet.Paste Destination:=Range(sInsCell) ' paste image fom clipboard
c1 = GetIndexPastedPicture() ' get index of pasted image (see above)
With ThisDBSheet.Shapes.Item(c1) ' correct the properties of the pasted image
.Top = Range(sInsCell).Top ' top alignment
.Left = Range(sInsCell).Left ' left alignment
.Name = sPicName ' assign new name
End With
Range("I18").Activate ' move focus from image
End Sub 'InsPicFromClipbrd