VBA snippet to attach an image

[Karthik Thandapani]

Hi,

Can anyone please provide a VBA code snippet to allow users to attach an image file into a range.

a) Open up file explorer
b) attach image file
c) add attached image file to a range("A1: H20")

Thanks,
Karthik

 

[Luke M]

Run the InsertImage macro. Note that workbook must be saved already (not a brand new workbook)

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

 

[Karthik Thandapani]

Rockingggggg.........!!! thanks @Luke M
it works like a breezeeee

 

[Karthik Thandapani]

@Hui : as you mentioned, it doesn't work the the new workbook. Can you please help to modify that to work with a newly created workbook too.
Actually this is an item to be forwarded to users. Thanks again!

 

[Luke M]

Recommend deleting these lines then:

    'Change current folder to be wherever this file is stored
   ChDir ThisWorkbook.Path

It just sets up the search folder to being in current workbook's folder. Not critical to code running, just a nice thing to have.