VBA to insert images from file path

[lalamorg]

I'm creating an invoice template that needs to show a product image for each line item. The Invoice sheet links to a Catalog sheet using vlookup for all item data. The data for the image is a filepath, however. I need the actual photo to display on the Invoice.

I've done some digging and found the macro info below. Using this, I could theoretically use vlookup to pull in the filepath and then run the macro to pull in the image. (I could then hide the column containing the file path so that only the image shows.)

When I test run the macro, however, it's returning... Compile Error: Syntax error.

I'm very new to VBA and can't figure out where the problem is. Anyone have any thoughts or spot the issue?

Here's the info on the code.....

The below code
. loops through each cell in the selected range
. reads the filepath from that cell
. inserts the referenced picture in the cell to the right of the cell:
. resizes the height and width of the picture to the cell's height

Sub InsertPicFromFile()
Dim cCell As Range

For Each cCell In Selection
If cCell.Value <> "" Then
On Error Resume Next
ActiveSheet.Shapes.AddPicture _
Filename:=cCell.Value, LinkToFile:=msoFalse,
SaveWithDocument:=msoTrue, _
Left:=cCell.Offset(ColumnOffset:=1).Left, Top:=cCell.Top,
Width:=cCell.Height, Height:=cCell.Height
End If
Next cCell
End Sub


To use that code:
1)Select a vertical range of cells containing complete paths to picture
files.
(Make sure the row heights are large enough to view the pictures.)

2)[Alt]+[F8].....shortcut for <tools><macro><macros>....Select:
InsertPicFromFile....Click [Run]

 

[Ron Coderre]

You are missing some line-continuation-characters (the underscores at the end of some of the lines).

Try this variation:

Sub InsertPicFromFile()
Dim cCell As Range
For Each cCell In Selection
If cCell.Value <> "" Then
On Error Resume Next
ActiveSheet.Shapes.AddPicture _
    Filename:=cCell.Value, LinkToFile:=msoFalse, _
    SaveWithDocument:=msoTrue, _
    Left:=cCell.Offset(ColumnOffset:=1).Left, Top:=cCell.Top, _
    Width:=cCell.Height, Height:=cCell.Height
End If
Next cCell
End Sub

Does that help?

 

[lalamorg]

YES!!! Thank you SO much!

 

[habdou]

Hi Ron,
I have a similar issue with: VBA to insert images from file path
Basically I have an Excel ‘Shopping list’ that I am doing for a client, where she chooses products from Products db., and she assign the list to a client from the Clients db.
The product database is exported to CSV Comma Delimited from a website.
Everything is working fine except the image part,
I’ve used the Data Validation/List for the ID and vlookup function like so: =IFERROR(VLOOKUP([ID],PRODUCTS,2,FALSE),"") to pull the rest of the data in the columns the image.
The image field is populated with: /users/manager/webapps/products/images/imagename.jpg…
Using front slash
How can I convert the above path to show the image instead of the path? and when it is viewed locally the path should show point to C:\inetpub\wwwroot\interiordesignonline\users\manager\webapps\products\images
With back splash?.
Thank you,
Another issue:
Using the formatted table ‘Ctrl + T’, how can I have the row height when tabbing or enter to inherit the row height from the above row, I am assigning the row height to 55?

 

[Al1882]

Hi - I found this really useful thanks. Is it possible to tweak this macro so that it will work over multiple worksheets?

In my spreadsheet I have photo filepaths in Cell A20 of each sheet, and I would like to insert the associated photo in Cell B20 of each sheet.

Unfortunately I do not know how to code in VBA, so I'm hoping someone might be able to help.

Many thanks, Al