I'm having challenges importing picture from access database into excel with vba. I can retrieve the picture from access but when I'm displaying them on excel it show only the picture filename, not the image.
It isn't possible to load pictures "into" an Excel cell object. An Image object is on a layer above the cells layer.
It will require VBA to loop through cells and place the image aligned somewhat near to the cell perhaps in the next column.
I won't have time to run and test this. The code should be somewhat along the lines of:
For Each cel In Selection
cel.Offset(0, 1).Select
ActiveSheet.Pictures.Insert(cel.Value).Select
Next cel
My favorite method is to use Remote Automation to run the code from Access. From Access VBA, set a Tools Reference to Excel and adjust the code to reference Excel.
----------> Consider This instead
Your cells would have to be pretty tall to show the images next to them.
You know how to put a Comment into a Cell? It has the little red corner, the mouse hovers over it and displays the comment? This is basically how to put the Image in a Comment instead of just text. Sorry I don't have more time to explain
Code:
Option Explicit
Function InsertCommentImage(title As String, absoluteFileName As String)
Dim commentBox As Comment
' Clear any comments before attempting to add them.
.ActiveCell.ClearComments
' Define the comment as a local variable and assign the file name from the
' cellAddress input parameter to the comment of a cell.
Set commentBox = Application.ActiveCell.AddComment
With commentBox
.Text Text:=""
With .Shape
.Fill.UserPicture (absoluteFileName)
.ScaleHeight 3, msoFalse, msoScaleFromTopLeft
.ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft
End With
' NOTE set the visible to True when you always want the image displayed, and
' to False when you want it displayed only when you click on the cell.
.Visible = False
End With
InsertCommentImage = title
End Function
This InsertCommentImage function has two arguments: a title that will appear in the cell the function is entered into, and a reference to the image's location and name.
Add the function into your workbook that contains the links.
1. Select the Developer tab from the main Ribbon and then choose Visual Basic (the first button on the left of the Developer tab). The VBA integrated development environment (IDE) window should appear.
2. Make sure that "VBAProject(your workbook name)" is highlighted in the "Project - VBA Project" pane (the left upper pane).
3. From the main VBA IDE menu, select Insert/Module and paste the function code in the large code pane that has opened up.
4. Close up the VBA IDE window and save the workbook. You can then use the function in the sheet with the links.
Hopefully, this is enough to get you started. Give this a try then please post a simple example if you need more help.
