Dynamically insert a picture.

[Guest]

I would like to take a cell value and use it to dynamically enter in a
picture (really a Visio diagram).

For Example:
- If the cell A2’s value was “A423â€
- It would look for the Visio diagram that corresponds to its name under
some folder structure like, “/some_folder/A423.vsdâ€
- Then insert that image/diagram into the Excel worksheet at a particular
area.

Does anyone have any ideas on doing this dynamically, if worse case would
like to have one macro that would insert multiple images, per each page in a
worksheet?

 

[Guest]

Here is code that will detect a change in cell A2 of any worksheet, will look
for the existence of the specified file, and will insert the picture on the
worksheet - put the path to the folder containing your pictures into
FolderPath and replace ".jpg" as needed:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim TestRange As Range, FolderPath as String, PicFilePath As String
FolderPath = "C:\FolderName\SubFolderName\"
Set TestRange = Intersect(Sh.Range("A2"), Target)
If Not TestRange Is Nothing Then
PicFilePath = Folderpath & Sh.Range("A2").Value & ".jpg" ' REPLACE .jpg
IF NEEDED
If Dir(PicFilePath) = "" Then
MsgBox "File " & PicFilePath & " could not be found", vbExclamation,
"CANNOT INSERT PICTURE:"
Else
Sh.Range("B2").Select
Sh.Pictures.Insert PicFilePath
End If
End If
End Sub

(Written in Excel XP, cannot say if it works for earlier versions)
HTH! K Dales