Label generator - getting started

[dourpil]

Hi all!

To improve work quality, we're working on a way to generate labels more easily.


Background:
Environmental testing is made almost everywhere in the company. When someone is getting ready for a series of tests, he has to print the labels he needs (they correspond to the rooms that he's going to be testing). ATM we have no real tool to generate the labels, which is an issue because it's quite a mess: we test rooms' walls, floors, ceilings, chairs, doors, ... You can have 3 tests on a wall and 2 tests on the second wall of the same room, ...

Goal:
Make this person's life easy by allowing him to check a few buttons, click a print command and be on his way to the printer and then to his tests.


Challenges:
I don't know if or how I'll be able to do a few things. The first (and probably one of the most important) is how data will be arranged. An Access database would probably be better here but I'm even worse in Access than I am in Excel
I'm not familiar with button controls and auto-populating stuff. This won't be too hard I think
I have no knowledge of VBA at all. The macro (I guess it'll be a macro) I'll be using should be quite basic but again, I don't know anything about that

Please see attached file, there are a few comments in there



Questions:
At the moment I'm not exactly sure where I am. As I said, layout will probably be important. I made a "linear" database. I don't know if e.g one table per Dept would be better?

My small table with "list of rooms - to be tested?" columns, as I said in the text box, should be automatic. I'm not sure what formulas I could use to display what I want to display with the associated checkboxes. (It shouldn't be too hard but I'm stuck with the data layout. It might need helper columns or something)

The output would - I guess - need VBA. As I said, the aim is to print labels so IDK if I should pre-format a few blank worksheets that would be populated with VBA code or if it would to it by itself (by pre-formatting I mean adapt rows height and columns width to the labels size).


Any comment, idea, chocolate bars appreciated

Regards,
Simon


PS: This is a cross post from excelforum. I thought afterwards Chandoo was perhaps a more suitable forum for a project (as opposed to a single formula question)

 

[dourpil]

Hi again,

I'm currently looking into Word mail merge to print my labels.
It could be an easy way of achieving what I'm trying to do.. However, having no knowledge of VBA I'm a bit stuck.

Assuming I have a 4 columns sheet which is my "mail merge data", I tried recording a macro in Word with the mail merge wizard.

I'd like to embed this into an Excel macro but I don't really know how to do it..

The code from Word macro is

Sub GenEtiq()
'
' GenEtiq Macro
'
'
ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
ActiveDocument.MailMerge.OpenDataSource Name:= _
"Z:\Techniciens\SDPL\etiquettes\generation etiquette.xlsx", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=Z:\Techniciens\SDPL\etiquettes\generation etiquette.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB" _
, SQLStatement:="SELECT * FROM `Sheet3$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Departement"
Selection.TypeParagraph
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Salle"
Selection.TypeParagraph
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Endroit"
Selection.TypeText Text:=" "
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Point"
Selection.WholeStory
Selection.Font.Size = 8

ActivePrinter = "\\beleprint001\BELEMDQA14"
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End Sub

 

[dourpil]

It doesn't seem like I'll receive an answer here but I might aswell keep updating as I advance. Who knows, perhaps someone will stop by and fulfill my wish :D

I couldn't get mail merge to work so I'm back to a 100%Excel project. I set up a few sheets as template for my labels and am now trying to figure out a macro to populate the labels.



Here is the logic that I think would work here. If someone could help me translate that into VBA code it'd be amazing

I'll ignore the exact set up I presented and will assume that 1 cell corresponds to 1 cell in my output.

1. Count entries in RecapTable (found something that does that! yeey!)
n = Worksheets("RecapTable").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count

((2. Divide that by 65 (which is the number of labels per sheet) and round at upper number. For example: if n=100, 100/65 = 1.54 so 2 sheets needed)) [maybe a useless thing to do]

[Assuming I only populate 2 cells]

If v is the row# in RecapTable,
If w is the column# in RecapTable,
If x is the row# in Etiq,
If y is the column# in Etiq,

v=1, w=1, x=1

for v=1 to n or to 65, [taking care of the 1st output sheet: we take the 65 first entries in RecapTable]
-y=1 [reset column# in output]
-for y=1 to 9 [Populating one row of labels at a time in output]
--cell(v,w) = cell(x+1,y)
--cell(v,w+1) = cell(x+2,y) [populating my cells]
--y=y+2 [moving 2 columns to the right to skip the separator column]
--v=v+1 [moving to the next entry in RecapTable]
-end [of looping through horizontal labels]
-x=x+3 [moving to the next row of labels]
end [of 1st sheet of ready-to-print labels]