Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Leitey
 
Posts: n/a
Default How do I name a picture as I paste it from VBA? (ActiveSheet.Past.

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.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: How do I name a picture as I paste it from VBA? (ActiveSheet.Past.

To name a picture as you paste it from VBA, you can use the following steps:
  1. Copy the picture to the clipboard using the following code:
    Formula:
    Selection.CopyPicture Appearance:=xlScreenFormat:=xlPicture 
  2. Create a new shape in Excel using the following code:
    Formula:
    ActiveSheet.Shapes.AddShape(msoShapeRectangle100100200200).Select 
  3. Paste the picture from the clipboard into the new shape using the following code:
    Formula:
    ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
    ActiveSheet
    .Paste 
  4. Rename the shape to "samplepicture" using the following code:
    Formula:
    ActiveSheet.Shapes("Rectangle 1").Name "samplepicture" 

So, the complete code to paste the picture from the clipboard and name it as "samplepicture" would be:

Formula:
Sub PasteAndNamePicture()
    
'Copy the picture to the clipboard
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    
    '
Create a new shape in Excel
    ActiveSheet
.Shapes.AddShape(msoShapeRectangle100100200200).Select
    
    
'Paste the picture from the clipboard into the new shape
    ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
    ActiveSheet.Paste
    
    '
Rename the shape to "samplepicture"
    
ActiveSheet.Shapes("Rectangle 1").Name "samplepicture"
End Sub 
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Earl Kiosterud
 
Posts: n/a
Default

Leitey,

When you paste, it'll be selected.

ActiveSheet.Paste
Selection.Name = "samplepicture"
--
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Leitey" wrote in message
...
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.



  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #5   Report Post  
Junior Member
 
Posts: 1
Default

Quote:
Originally Posted by Dave Peterson View Post
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't Copy and Paste or Paste Special between Excel Workbooks wllee Excel Discussion (Misc queries) 5 April 29th 23 03:43 AM
Can an Excel formula be created to paste a picture in a cell? bonita0914 Excel Worksheet Functions 2 April 28th 05 06:11 PM
Textboxes and "Paste as picture" Eric Charts and Charting in Excel 6 March 5th 05 09:55 PM
insert picture BillGwyer Excel Discussion (Misc queries) 1 March 4th 05 07:37 PM
how to paste a picture into the header of spreadsheet in Excel 20. Excel programming questions Excel Discussion (Misc queries) 1 February 16th 05 12:33 AM


All times are GMT +1. The time now is 04:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"