Make picture extension flexible

[YasserKhalil]

Hello everyone
I have a macro that inserts pictures and it work well
At this line

Code: Select all

With .Shapes.AddPicture(Filename:=sPath & ws.Cells(r + c - 2, 1).Value & ".jpeg", LinkToFile:=False, SaveWithDocument:=True, Left:=cPic.Left + 5, Top:=cPic.Top + 5, Width:=cPic.Width - 10, Height:=cPic.Height - 10)

How can I make the extension part is flexible to accept "jpg" or "jpeg" or "png" ..?

[HansV]

I'd add the extension in the cells. You can then use

Code: Select all

With .Shapes.AddPicture(Filename:=sPath & ws.Cells(r + c - 2, 1).Value, LinkToFile:=False, SaveWithDocument:=True, Left:=cPic.Left + 5, Top:=cPic.Top + 5, Width:=cPic.Width - 10, Height:=cPic.Height - 10)

[YasserKhalil]

The idea is that there are multiple mixed pictures with multiple extensions, so I need to check each extension and if it is found then to insert it ..

[HansV]

So there could be files such as Yasser.jpeg, Yasser.jpg and Yasser.png? If so, which one to use? The first that we find?

[YasserKhalil]

No the matter is of the extension difference
Examples: Ahmed.jpg - Yasser.jpeg - Hany.png
But the names are listed in column A like that "Ahmed - Yasser - Hany", so in this case, I can't specify one extension and at the same time I can't put the extensions in another column

[HansV]

Why don't you specify the extensions in column A with the filenames? Ahmed.jpg etc.?

[YasserKhalil]

The report that came would be without extension .. Is it difficult to implement more than extension
I thought of a way but didn't work for me. Which is to loop through array of extensions like that Array(".jpg",".jpeg",".png") then to use loop and inside loop to use Dir to make sure of the existence of the path and if not empty then exit sub after storing the suitable extension for the path

[HansV]

What a silly useless report!

Add two declarations:

Code: Select all

    Dim sFile As String
    Dim vExt As Variant

and use something like this:

Code: Select all

        sFile = sPath & ws.Cells(r + c - 2, 1).Value
        For Each vExt In Array(".jpeg", ".jpg", ".png")
            If Dir(sFile & vExt) <> "" Then
                With .Shapes.AddPicture(Filename:=sFile & vExt, _
                        LinkToFile:=False, SaveWithDocument:=True, _
                        Left:=cPic.Left + 5, Top:=cPic.Top + 5, _
                        Width:=cPic.Width - 10, Height:=cPic.Height - 10)
                    ' Do something with the shape
                    '...
                End With
                Exit For
            End If
        Next vExt

[YasserKhalil]

Amazing. I was so close but couldn't make it work. Thank you very much for this elegant trick.