October 7, 2018

Create Product Catalog Automatically

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

2 thoughts on “Create Product Catalog Automatically

  1. Dear Dinesh,
    I watched 1 of your videos today on incrementally increasing a number in a cell by 1.
    I was able to replicate this successfully, thank you.
    However, I want to increase a whole column of numbers by 1 in an excel data table, but whatever I try it does not work.
    To give you some context, my excel database is a fixed asset register and for each record in the database I want to increase the month depreciation column by 1 so that month 1 becomes month 2 etc. This then calculates the aggregate depreciation and gives me the NBV. The database is dynamic and ultimately will have several thousand records. I want an automatic means of increasing the whole range by 1.
    Can you please tell me how I can achieve this please.
    Many thanks for your excellent videos.
    Ian Gaweda

  2. Dear Dinesh,

    I have a question, my computer dose not offer just excel, i only have (libreoffice calc) And i am trying to put together a product catalog for my parents business, is there any way you can help me with this??

Comments are closed.