Prev: Select list box entries in multiple rows?
Next: Macro to copy subtotaled data
From: Peter T on 14 Apr 2010 09:52
OK. If you only store "138-58798 how will you know in future if it refers to
138-58798.jpg or 138-58798.gif

If it's no problem to store 138-58798.jpg in the cell visible to user,
before adding the picture to the sheet do something like this

Dim pos As Long
Dim sFile As String, sPicName As String

sFile = "138-58798.jpg"
pos = InStrRev(sFile, ".")
sPicName = Left$(sFile, pos - 1)

Apply sPicName as the name of the newly inserted picture. Later when you
want to check if the picture exists, use the same code to convert the name
in the cell (with the extension) from the name of the possible inserted
picture

On error resume next
Set pic = Activesheet.Pictures(sPicName)
on Error goto 0

If pic is nothing then
' code to insert a picture
Else
' code to reformat the picture
End if

If you do not want the extension to be visible in the list to your users,
you will need to include a separate Lookup table along the lines of my first
suggestion.

Regards,
Peter T




"marc747" <marc747(a)excite.com> wrote in message
news:ddc70f78-83e1-42d6-b2d9-c4446ead2817(a)5g2000yqj.googlegroups.com...
Hi,
Sorry for that, I just have hard time explaining....

The actual picture files have extensions most of which are ".jpg" some
".gif"
All pictures are in the same folder "C:\mypictures\
Default picture "C:\mypictures\Picture_not_Available.jpg"
The file names that I entered in Excel are without extensions,
example: actual picture name "138-58798.jpg" name entered in excel
"138-58798"

Thanks.
-----------------------------------------------------------



On Apr 13, 2:15 am, "Peter T" <peter_t(a)discussions> wrote:
> I am finding all this very difficult to understand. Do you mean -
>
> a) the file-names themselves do not have an extension, or
> b) do you mean the file-names do have an extension but you removed the
> extensions from the list that papers in cells.
>
> I think I already assume the shapes do not have extensions.
>
> Are all the files in the same folder or different folders, where is/are
> the
> path(s) stored.
>
> Regards,
> Peter T
>
> "marc747" <marc...(a)excite.com> wrote in message
>
> news:36ad8b3b-0e83-47e5-ba2f-28a4e192fd47(a)b33g2000yqc.googlegroups.com...
> Hi,
> Yes, my file names are without file extensions. I have the file names
> in a validation list in Cell "L21" and everytime that I pick a file
> name from that list the picture in cell "L9" changes and if no picture
> is availbale for that file name a default picture is inserted "C:
> \mypictures\Picture_not_Available.jpg".
>
> Thanks.
>
> On Apr 11, 4:56 am, "Peter T" <peter_t(a)discussions> wrote:
>
>
>
> > I don't follow what you are asking in addition to what I already
> > suggested.
> > Try and explain the logic of how you want to get from a file name to the
> > name of a picture, eg, did you already name the picture as that of the
> > filename without the extension.
>
> > Better still, describe why you cannot use the "first" code and you
> > cannot
> > make the "last" one work. Not sure what you mean by first and last,
> > there
> > was only one set of code.
>
> > Regards,
> > Peter T
>
> > "marc747" <marc...(a)excite.com> wrote in message
>
> >news:1fab5e4f-76e1-43a6-8a59-9373ea12123a(a)e7g2000yqf.googlegroups.com...
> > Hi,
>
> > The first Code is working but not the way that I can use. The last one
> > I can't make it work.
>
> > Is it possible the keep my original codes most of it as is and only
> > add a few lines of code so that instead of the macro looking for the
> > picture by name and extension to only look for the name of the picture
> > file by name only no matter what the extension is. I appreciate your
> > help....
>
> > Thanks
>
> > On Apr 8, 5:08 am, "Peter T" <peter_t(a)discussions> wrote:
>
> > > Well the sample code I suggested does work, though it may not with
> > > your
> > > implementation of it.
>
> > > From what I understand, I'd suggest you maintain a Lookup table
> > > somewhere,
> > > perhaps on a hidden sheet or columns.
>
> > > UserDisplayName : FileName
> > > Green Apples : grnApples.jpg
> > > Pink Roses : pnkRoses.jpg
> > > unknown : Picture_not_Available.jpg
>
> > > ' warning air-code !
> > > s = Range("L9" ).text
>
> > > arr = range("fileList").value ' where fileList refers to the lookup
> > > table
>
> > > For i = 1 to ubound(arr)
> > > if s = arr(i,1) then
> > > sFile = arr(i,2)
> > > ExitFor
> > > end If
> > > Next
>
> > > ifLen(sFile) = 0 then
> > > sFile = Picture_not_Available.jpg
> > > end if
> > > pos = InStrRev(sFile, ".")
> > > sPicName = Left$(sFile, pos - 1)
>
> > > on error resume next
> > > Set shr = activesheet.Pictures(sPicName).ShapeRange
> > > on error goto 0
>
> > > if shr is nothing then
> > > sFile = thisworkbook.path & "\" & sFile
> > > Set shr = activesheet.Pictures.Insert(sFile).ShapeRange
> > > End if
>
> > > shr.name = sPicName
>
> > > etc ' change properties of shr for location, size etc
>
> > > Regards,
> > > Peter T
>
> > > "marc747" <marc...(a)excite.com> wrote in message
>
> > >news:61675848-09eb-4125-8c2e-caa02562fec4(a)q23g2000yqd.googlegroups.com...
> > > Hi,
>
> > > The code did not work.
> > > Basically what I use the code for is that I have in Cell "L21" a
> > > Validation List with Picture names and every time I select a name from
> > > the list the picture in cell "L9" changes and insert the picture with
> > > the name on the list that I selected and incase there is no picture
> > > with the name that I selected it will insert a default picture called
> > > "Picture_not_Available.jpg"
>
> > > The thing is that I don't want the name of the pictures on my list to
> > > show the picture file name with the file extension, therefore I wanted
> > > to make a change in the Macro that I have so that it will look for the
> > > picture by name only no mater what the file extension is.
>
> > > Thanks,
>
> > > Armond
>
> > > *******************************************************
>
> > > On Apr 7, 2:13 am, "Peter T" <peter_t(a)discussions> wrote:
>
> > > > Just for what you are doing you don't need the name at all (though
> > > > you
> > > > might
> > > > want to give it a name and store it for some future process)
>
> > > > Dim shr As ShapeRange
>
> > > > 'code
>
> > > > Set shr = activesheet.Pictures.Insert(filename).ShapeRange
>
> > > > now replace all "Selection.ShapeRange" with "shr"
>
> > > > Hmm, had anther glance at your code, not sure I quite follow what
> > > > you
> > > > aiming
> > > > to do overall. If the above is not enough revert back and explain.
>
> > > > Regards,
> > > > Peter T
>
> > > > "marc747" <marc...(a)excite.com> wrote in message
>
> > > >news:04edf68e-1568-49ee-8653-78677f424c8f(a)5g2000yqj.googlegroups.com...
>
> > > > > Hi,
>
> > > > > I have the following Macro but it seems that I need the name of
> > > > > the
> > > > > picture along with the extension in order to work, is there a way
> > > > > to
> > > > > include the extensions (.gif, .jpg, .jpeg, and more that I don't
> > > > > know ........) into the Macro so that I don't need to include the
> > > > > picture name with the extension.
> > > > > Thank You.
>
> > > > > -----------------------------------
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > Dim myScale As Double
> > > > > If Target.Address <> "$L$21" Then Exit Sub
>
> > > > > 'Select the cell where the picture is placed
> > > > > Application.EnableEvents = False
> > > > > On Error Resume Next
> > > > > ActiveSheet.Shapes("KnownPictureName").Delete
> > > > > On Error GoTo 0
>
> > > > > Range("L10").Select
> > > > > 'Insert the picture
> > > > > On Error GoTo NoPic
>
> > > > > 'this is the one with the link to the file that I need the
> > > > > extension
> > > > > ActiveSheet.Pictures.Insert("C:\Temp\Pix\" &
> > > > > Range("L21").Value).Select
>
> > > > > GoTo GotPic
> > > > > NoPic:
> > > > > ActiveSheet.Pictures.Insert("C:\Temp\Pix\No Pic.jpg").Select
> > > > > GotPic:
> > > > > 'scale the picture to the width of the column
> > > > > myScale = 42 / Selection.ShapeRange.Height
> > > > > Selection.Name = "KnownPictureName"
> > > > > Selection.ShapeRange.ScaleWidth myScale, msoFalse,
> > > > > msoScaleFromTopLeft
> > > > > Selection.ShapeRange.ScaleHeight myScale, msoFalse,
> > > > > msoScaleFromTopLeft
> > > > > Range("L22").Select
>
> > > > > Application.EnableEvents = True
> > > > > End Sub
> > > > > ------------------------------------------------ Hide quoted
> > > > > text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -


First  |  Prev  | 
Pages: 1 2 3
Prev: Select list box entries in multiple rows?
Next: Macro to copy subtotaled data