excel 2013 inserting pictures automatically

Question

I have a folder which has picture files, pic1, pic1a, pic2, pic2a....etc upto pic22a

I have a spreadsheet which has spaces for each picture to be placed in. The pictures change on a daily bases but filenames stay the same. Each day I have to print a pdf of the spreadsheet with updated pictures. is it possible to have the spreadsheet auto-update with the new pictures instead of manually changing the pictures each day. I thought this would be relatively simple, however apparently not.

for simplicity lets say the pictures are to be located A1:B2, C1:D2 etc and pictures are located in folder "pictures-here\"

can anybody offer some assistance, or know of a method I can utilise

Many thanks John

Answer 1

This is a preliminary solution. Try these two macros.

Sub Del_Pics()

    Dim MyPics As Object
    For Each MyPics In ActiveSheet.Pictures
        MyPics.Delete
    Next MyPics

End Sub

Sub Add_Pics()
   On Error Resume Next
   Range("C1").Select
   ActiveSheet.Pictures.Insert ("C:\Temp\pic1.jpg")
   Range("F1").Select
   ActiveSheet.Pictures.Insert ("C:\Temp\pic1a.jpg")
End Sub

The first Macros named Del_Pics will delete all inserted pictures from the file. It's assumed that you do not have any static inserted images in the file that you want always be present therein.

Second macro Add_Pics adds respective pictures at respective cells in the file. You need to modify this to specify the exact file name & path and cell location as applicable in your sheet. Repeat for all the Pics. A more smarter solution to loop thru all files in a folder and place then at respective Cells is also possible though.

This is a preliminary solution. This does not verify the picture size and adjust the height etc.

Press ALT + F11 to access VBA Editor and insert a Module from Insert Menu and insert the above code into it.

When you need to run the same. Press ALT + F8 to access Macro dialog box and select and run the intended Macros. You may like to combine the two into a single code as well. Place the Del_Pics code first.