VBA Beginner: Import multiple pictures

VBAChild

New Member
Joined
Aug 4, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hallo everyone! I'm a total VBA noob (I started learning yesterday)... I need to import something like 450 images into Excel.
I have a column with a series of Type Marks (CHA001, CHA002, STO001....) starting from cell A1. I need to import images with the same names (CHA001.jpg, CHA002.jpg, STO001.jpg...) into the neighbouring cells, in column B.

I've tried a couple of different scripts, however, I don't have all the images and I think this is why my script is failing. I'm looking for a way to make VBA skip the cells which have no corresponding image and move on to the next one.

One option:
VBA Code:
Sub AddImages()

Dim i As Long
Dim ffePic As Picture
Dim imgLocation As String
Dim typeMark As String
   
Application.EnableEvents = False
Application.ScreenUpdating = False

For i = 2 To 459

typeMark = Worksheets("Sheet1").Cells(i, 1).Value
imgLocation = "C:\Users\Raina Armstrong\Pictures\JPEG\" & typeMark & ".jpg"

With Worksheets("Sheet1").Cells(i, 2)
Set ffePic = ActiveSheet.Pictures.Insert(ImageLocation)
Set CellLocation = ActiveSheet.Cells(i, 2)

    ffePic.Top = CellLocation.Top + (CellLocation.Height / 2) - (ffePic.Height / 2)
    ffePic.Left = CellLocation.Left + (CellLocation.Width / 2) - (ffePic.Width / 2)
    ffePic.ShapeRange.LockAspectRatio = msoTrue
    ffePic.Placement = xlMoveAndSize
    ffePic.ShapeRange.Height = 100

End With

Next

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Another:
VBA Code:
Sub AddImages()

For Each r In rng
    fName = Dir(fPath)
    Do While fName <> ""
        If fName = r.Value Then
            With ActiveSheet.Pictures.Insert(fPath & fName)
                .ShapeRange.LockAspectRatio = msoTrue
                .Top = r.Offset(0, -2).Top
                .Left = r.Offset(0, -2).Left
                If .ShapeRange.Width > Columns(2).Width Then .ShapeRange.Width = Columns(2).Width
                Rows(r.Row).RowHeight = .ShapeRange.Height
            End With
        End If
        fName = Dir
    Loop
Next r

Application.ScreenUpdating = True
Application.ScreenUpdating = True

End Sub

Any help here would be greatly appreciated!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,049
In the first macro add these lines on either side of the With .... End With block:
VBA Code:
If Dir(imgLocation) <> vbNullString Then


End If
In this line:
VBA Code:
Set ffePic = ActiveSheet.Pictures.Insert(ImageLocation)
ImageLocation is undefined - change it to imgLocation. Always use Option Explicit so the compiler finds these type of errors.
 

Forum statistics

Threads
1,144,342
Messages
5,723,824
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top