Automatically filling a label template in Excel

Ben171

Board Regular
Joined
Jul 2, 2021
Messages
66
Hi all, sorry for the long post... trying to make it as clear as possible

I have created a simple userform/database in excel. Users fill in a userform:

1631519459342.png

Once saved, data is entered into the database:


1631521667114.png


I then created a template document on Word, which includes bookmarks to automatically pull through the data to the correct box of the label.

1631521792121.png



I created the following piece of code which tells the user to enter the works order number (as this field is unique). It then finds this row of the database and transfers the data from that row into the bookmarks in the word template and sends it to the label printer

VBA Code:
im wd As Object 'Word Application
Dim wdDOC As Object 'word document
Dim iRow As Long 'Variable to hold the starting row and loop through all records in database
Dim sh As Worksheet 'worksheet variable to refer  to where database is stored
Dim myValue As Variant
Dim WorksOrder As String
Dim Found As Boolean  '******** Indicator for find

'Start word as new document

Set wd = CreateObject("Word.Application")


'Get user entered WorksOrder Number

WorksOrder = frmForm.TextBox27.Value


'Set worksheet

Set sh = ThisWorkbook.Sheets("Database")

iRow = 2 'row in which data starts from in database

Found = False

Do While sh.Range("A" & iRow).Value <> ""  'loop through until no data is found (last row of database)
 
If WorksOrder = sh.Range("D" & iRow).Value Then

 Found = True

'opening word template

Set wdDOC = wd.Documents.Add("\\server22\pe_projects\Engineering\Receiver Mounted Stationary Screw Compressors\9_RM Database\RM Labels V3.docm")

wd.Visible = False

'code to insert values from database to bookmarks in word

wd.Selection.GoTo what:=wdGoToBookmark, Name:="ModelNo"
wd.Selection.TypeText text:=sh.Range("O" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="PartNo"
wd.Selection.TypeText text:=sh.Range("C" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="MaterialNo"
wd.Selection.TypeText text:=sh.Range("F" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="SerialNo"
wd.Selection.TypeText text:=sh.Range("E" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="BarRating"
wd.Selection.TypeText text:=sh.Range("T" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="ModelNo2"
wd.Selection.TypeText text:=sh.Range("O" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="PartNo2"
wd.Selection.TypeText text:=sh.Range("C" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="MaterialNo2"
wd.Selection.TypeText text:=sh.Range("F" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="SerialNo2"
wd.Selection.TypeText text:=sh.Range("E" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="BarRating2"
wd.Selection.TypeText text:=sh.Range("T" & iRow).Value

'code to delete the existing bookmarks from wordfile

On Error Resume Next

wdDOC.Bookmarks("PartNo").Delete
wdDOC.Bookmarks("SerialNo").Delete
wdDOC.Bookmarks("ModelNo").Delete
wdDOC.Bookmarks("PartNo2").Delete
wdDOC.Bookmarks("SerialNo2").Delete
wdDOC.Bookmarks("ModelNo2").Delete

'save file with new name

wdDOC.SaveAs2 ThisWorkbook.Path & "\Box Labels\" & sh.Range("D" & iRow).Value, 17

'Tell word to use label printer

wd.Application.Run "Module1.LabelPrinter"

'Print document

wdDOC.PrintOut

'switch default back to normal printer

wd.Application.Run "Module1.RicohPrinter"

'close the word file

wdDOC.Close False

'release memory of word doc

Set wdDOC = Nothing

Exit Do

End If

iRow = iRow + 1

Loop

wd.Quit 'close MS Word

Set wd = Nothing 'Release memory allocated to WD

If Found = True Then

Else
MsgBox ("Works Order Number Not Found")
End If

End Sub

This works great, if the works order number is found, it inputs the correct data and sends it to the printer. However, the problem I have is that the labels we use are 96mm wide and 55mm high. This means the page orientation of the template is always landscape. And due to a problem with Word, which i have found multiple posts about e.g. Prevent Word auto rotate from portrait to landscape

You are unable to change this to portrait. The issue with this is that the labels print out the wrong way round when printed and there is no way to stop this (from what i've researched).

So i thought i will try to recreate this template in excel as it would give me more control. My question is however what would be the best way to recreate this word template in Excel? What is the best method of getting the correct data from the database into the template through the use of the unique field of works order number?
 
Last edited:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,148,015
Messages
5,744,349
Members
423,863
Latest member
teehexcel

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