Hi guys,
So I have a spreadsheet of like 400 items and I want to know what each of them looks like so I use photo comment (Insert Comment -> Format Comment -> Fill Color -> Picture).
But the problem is, this method uses local file only. After I upload my spreadsheet and download it from another PC, I cannot see the photos because they are local files.
I am quite new to Excel so please be elaborate in your answers/explanations.
I appreciate everyone's effort to help! Thank you!
slim_shyda,
Welcome to the Forum.
One way would be to load all your pictures onto a worksheet, or worksheets so they will go with the Excel file.
To get the fullpath of your picture files, right click on any picture file in the folder you want to load onto
your Excel worksheet. Select 'Properties' and in the line listed as 'Location' will be the fullpath for that folder.
Put that between the quotes in the code below, ie. Folderpath = "C:\Users\you\folder1"
I have annotated (with a single quote = comment) the lines that you can modify to change the sheet name
where the pictures will be placed, the cell height and width, and the picture size (height and width).
Please TEST the code FIRST in a COPY of your workbook (always make a backup copy before trying
new code, you never know what you might lose).
1. Copy the below code below
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu, then press M to insert a Standard Module, OR...
4a. On the menu bar at the top , click on Insert > Module
You will see a Module1 added to the workbook (pane on left side), and on the right side you now
have a white screen with the cursor flashing.
5. Where the cursor is flashing, paste the code.
6. Close the Visual Basic Editor and Go back to the spreadsheet. OR...
6a. Press the keys ALT + Q to exit the Editor, and return to Excel
7. Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled
workbook with the file extension .xlsm. The next time you open your workbook answer the "do you
want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) .
8. To run the macro from Excel, press ALT + F8 to display the Run Macro Dialog Box, then
Double Click the macro's name, then select 'RUN'.
Column A will have the filename, and column B will be the picture.
If your pictures will be coming from more than one folder, you will have to modify the code for each folder.
After running the code for folder 1, you will need to insert 2 columns before column A, ie. select columns A and B
at the top thereof, then right click and select 'insert'. Now the next group of pictures will have some place to go
without writing over the first group of pictures. OR…
You can change the worksheet name and put the next group of pictures on a new sheet…
ie. Sheets("Object2").Activate Don't forget you must create that worksheet BEFORE running the code.
In either case, you will need to change the fullpath for folder2, ie. Folderpath = "C:\Users\you\folder2"
So hold down the 'Alt_F11' keys...which should open the VB Editor showing the code below, and change one or both lines.
Close the Editor and save the file. It might be a good idea to comment out the line for the previous fullpath for folder1
and make a new line below (without the single quote) for the fullpath for folder2 incase you need to add or delete pictures
at a later date from folder1.
This code will load ALL the pictures in the folder to the sheet you designate.
I copied it from the following link:
Excel-VBA : Insert Multiple Images from a Folder to Excel Cells
Perpa
Code:
Sub AddOlEObject()
Dim mainWorkBook As Workbook
Application.ScreenUpdating = False
Set mainWorkBook = ActiveWorkbook
Sheets("Object").Activate [COLOR="#FF0000"] 'Change the sheet name from "Object" to the sheet name where you want your pictures to go[/COLOR]
Folderpath = "C:\Users\you\folder1" [COLOR="#FF0000"]'Change the folderpath to wherever your pictures are coming from[/COLOR]
Set fso = CreateObject("Scripting.FileSystemObject")
NoOfFiles = fso.GetFolder(Folderpath).Files.Count
Set listfiles = fso.GetFolder(Folderpath).Files
For Each fls In listfiles
strCompFilePath = Folderpath & "\" & Trim(fls.Name)
If strCompFilePath <> "" Then
If (InStr(1, strCompFilePath, "jpg", vbTextCompare) > 1 _
Or InStr(1, strCompFilePath, "jpeg", vbTextCompare) > 1 _
Or InStr(1, strCompFilePath, "png", vbTextCompare) > 1) Then
counter = counter + 1
Sheets("Object").Range("A" & counter).Value = fls.Name
Sheets("Object").Range("B" & counter).ColumnWidth = 18 [COLOR="#FF0000"]'Adjust to fit your pictures[/COLOR]
Sheets("Object").Range("B" & counter).RowHeight = 80 [COLOR="#FF0000"] 'Adjust to fit your pictures[/COLOR]
Sheets("Object").Range("B" & counter).Activate
Call insert(strCompFilePath, counter)
Sheets("Object").Activate
End If
End If
Next
mainWorkBook.Save
Application.ScreenUpdating = True
End Sub
Function insert(PicPath, counter)
'MsgBox PicPath
With ActiveSheet.Pictures.insert(PicPath)
With .ShapeRange
.LockAspectRatio = msoTrue
.Width = 50 [COLOR="#FF0000"]'Adjust to change the WIDTH of your pictures[/COLOR]
.Height = 70 [COLOR="#FF0000"]'Adjust to change the HEIGHT of your pictures[/COLOR]
End With
.Left = ActiveSheet.Range("B" & counter).Left
.Top = ActiveSheet.Range("B" & counter).Top
.Placement = 1
.PrintObject = True
End With
End Function
