• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

show image in a range

K

Karthik Thandapani

Member
Hi @Hui / experts:

http://forum.chandoo.org/threads/vba-snippet-to-attach-an-image.19404/#post-116937

above link helps to attach an image and to fit in a particular range. I am seeking help to expand the below code a bit to suit my another requirement.

Code: 
Sub InsertImage()
Dim myFilePath As String

myFilePath = RetrieveFileName
If myFilePath = "" Then Exit Sub 'User cancelled

ActiveSheet.Shapes.AddPicture myFilePath, False, True, Range("A1").Left, Range("A1").Top, Range("A1:H20").Width, Range("A1:H20").Height

End Sub
Private Function RetrieveFileName()
'obtained from:
'http://www.ozgrid.com/VBA/ExcelsDialogBoxes.htm


Const myFilter      As String = "Any Files (*.*),*.*"
Dim sFileName      As String
    'Change current folder to be wherever this file is stored
  ChDir ThisWorkbook.Path
    'Show the open dialog and pass the selected file name to the String variable "sFileName"
  sFileName = Application.GetOpenFilename(FileFilter:=myFilter, Title:="Picture Select", MultiSelect:=False)

    'They have cancelled.
  If sFileName = "False" Then Exit Function

    RetrieveFileName = sFileName
End Function

a) I have image files saved in a particular folder. By default pic1 should load into range("A1:H20")
b) Can we have next button at right and previous button in the other side so as soon as we click, that deletes the existing image file and loads the next?

Please help. Thanks!
 
Well, this looks familiar. ;)
Are the names of the images stored within the workbook, or perhaps sequential in nature (Pic1.jpg, Pic2.jpg, etc.). It would also help to know what file type (bmp, jpg, gif, etc.) that you are using.
 
Hmm, the random numbers could be a problem. I take it that means they are like:
DSC_456
DSC_654
DSC_756

Or are they sequential?

If truly random, one plan of attack would be to have the workbook search through current folder, build list of all *.jpg files, and then use that list as reference point for determining which picture is "next/previous".
 
Sure thing. Here's an example to list all files of given extension.
Code: 
Sub ExampleList()
'Creates a list of all files with known extension
Dim myExtension As String
Dim fName As String
Dim fPath As String
Dim recCount As Long

'Extension to find
myExtension = ".jpg"
'Folder to search
fPath = "C:\My Documents\"


'String checks
If Right(fPath, 1) <> "\" Then
    fPath = fPath & "\"
End If
If Left(myExtension, 1) <> "." Then
    myExtension = "." & myExtension
End If

Application.ScreenUpdating = False
recCount = 1

Range("A:A").ClearContents
'Loop through all files
fName = Dir(fPath & "*" & myExtension)
Do Until fName = ""
    Cells(recCount, "A") = fName
    recCount = recCount + 1
    fName = Dir()
Loop
Application.ScreenUpdating = True

End Sub
 
@Luke M : yes, it lists extension of all those pics.
sorry for the trouble. Can you also please provide a code snippet where it shows images one by one.
Can I have a next & previous button and the code can be tied to it? Please help
 
thanks @Luke M . Works like a breeze.
I hope this will work with a new workbook too (unsaved workbook). Incase if I make this as an item and send as an attachment via email.
Many thanks!
 
Should...assuming the folder path you set it somewhere common, like a network location.
 
You must log in or register to reply here.
Back
Top