We all have a task that we hate doing. Adding a batch of 100 photographs in excel that has to be shared with someone, be it a boss or a client.

I was tensed too, tired of adding photos to the excel, so I went out searching for a code that will help me add files to an excel. After much research, I finally found a code that would help me list down the images in an excel file.

So all you need to do is take the following steps and run the script.

1. Open a new Microsoft excel document.

2. Enable macro in excel if not already enabled.

3. Go to ‘developer’ tab.

4. Click on ‘Visual basics’.

5. Select the sheet which you want to work on.

6. Double click to open the code notepad.

(This is how your screen will look)

7. Paste the code.

8. Save the file and go back to the excel sheet.

9. Press alt +f8

10. Click on Run

11. Choose the files you want to import

12. Click insert.

13. Wait…

Once you click insert, depending on the size and number of files, the images will get added to the excel. The height is currently set to 100 units, which can be changed in the coding. Also, remember, loading of images may take time depending on their weight, as the code first adds the files and then resizes them.

(And this is how it would look after the code is run)

__________________________________________________________

VB Code

Sub Test()

   

    Dim newPicture As Shape

    Dim nextPosition As Double

   

    Application.Goto Reference:=Range(“A:A”).End(xlUp)

   

    If Application.Dialogs(xlDialogInsertPicture).Show Then

       For Each newPicture In ActiveSheet.Shapes

           With newPicture

               .LockAspectRatio = msoTrue

               .Height = 100# ‘Adjust as needed

               .Top = nextPosition

               .Left = 0#

               nextPosition = Cells(.BottomRightCell.Row, 1).Offset(1).Top + Cells(.BottomRightCell.Row, 1).Offset(1).Height

           End With

       Next

    Else

       MsgBox “Cancel pressed”

    End If

   

End Sub

__________________________________________________________

If you like this blog please follow me on Twitter @buggingweb, like the Facebook page and add us in your Google Profile.