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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

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??

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.