A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do I name a picture as I paste it from VBA? (ActiveSheet.Past.



 
 
Thread Tools Display Modes
  #1  
Old July 5th 05, 07:57 PM
Leitey
external usenet poster
 
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.
Ads
  #2  
Old July 5th 05, 09:03 PM
Earl Kiosterud
external usenet poster
 
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.



  #3  
Old July 5th 05, 09:18 PM
Dave Peterson
external usenet poster
 
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
  #4  
Old April 12th 13, 11:12 AM
NikolayfromSPB NikolayfromSPB is offline
Junior Member
 
First recorded activity by ExcelBanter: Apr 2013
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("D15")

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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can an Excel formula be created to paste a picture in a cell? bonita0914 Excel Worksheet Functions 2 April 28th 05 06:11 PM
Can't Copy and Paste or Paste Special between Excel Workbooks wllee Excel Discussion (Misc queries) 4 April 5th 05 07:51 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:54 PM.


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