How to create a product catalog automatically using VBA. We can use VBA or Visual Basic for Applications to automate the description of products, insertion of thumbnails and create a hyperlink to the larger image underlying the thumbnail. Watch the video:

Watch this video on YouTube.

Here’s the complete VBA code to automate the process of product catalog creation in Excel:

Sub ProductNameCatalog()
‘we declare a few variables
Dim i As Long
Dim ProductName As String
Dim LocT As String
Dim LocP As String
Dim MyFileName As String

LocT = “c:\Photos\Thumbnails\”
LocP = “c:\Photos\”
MyFileName = LocT & “*.jpg”

Application.EnableEvents = False
Application.ScreenUpdating = False

Range(“A1”) = “Description”
Range(“B1”) = “Thumbnail”
Range(“C1”) = “Hyperlink”

Range(“A1:C1”).Select
With Selection.Font
.Name = “Arial”
.FontStyle = “Bold”
.Size = 14
.ColorIndex = xlAutomatic
End With

i = 1
‘On Error GoTo 0
ProductName = Dir(MyFileName, vbNormal)
Do While ProductName <> “”
i = i + 1
Range(“B” & i).Select
ActiveSheet.Pictures.Insert(LocT & ProductName).Select
With Selection.ShapeRange
.LockAspectRatio = msoTrue
‘ .PictureFormat.Brightness = 0.5
‘ .PictureFormat.Contrast = 0.5
‘ .PictureFormat.ColorType = msoPictureAutomatic
End With

Range(“C” & i).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:=LocP & ProductName, TextToDisplay:=ProductName
MyPos = InStr(Range(“C” & i), “.”)
Range(“A” & i) = Left(Range(“C” & i), MyPos – 1)
ProductName = Dir
Loop

Columns(“B:B”).Select
Selection.RowHeight = 30
Selection.ColumnWidth = 10
Columns(“A:C”).AutoFit
Range(“M1”).Select

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Download a sample file