|
The garden center and nursery where I work has had a need for several years,
to find a way to print color signs for the plants we carry. Sending the
information to a publisher is cost prohibitive and we add at least 300
new plants every year to our offerings.
I had already developed a catalog application to maintain information about
our plants. We also had a folder filled with pictures of each of the
plants we carry. In the catalog database I had stored the name of the image
object along with the name of the plant so that I could retrieve the image
and display it when maintaining the plant information. What I needed was
a way to format a 7" x 5" sign (2 per page) using essentially the
same information and image as I had in the database.
My first attempt at creating signs involved setting up an Access report.
This quickly became a problem because Access is really not designed to
format documents using text-wrapping. It also does not provide a means
to anchor a graphic on the page. Each time the text control containing the
plant's name would grow beyond 2 lines (when a plant name was very long,
for example), it would move the graphic and all subsequent text controls
down on the page. With
controls moving all over the place and images falling off the page, the
signs were not looking very professional. Finally, it was impossible
to print 2 signs per letter-sized page and to ensure that each sign had the
proper top, bottom and side margins, consistently.
I started researching what I could do with other Microsoft products and
ODBC (Open Database Connectivity). I finally found the solution by using
a combination of Word, Access and VBA. Here are the steps I took to solve
my problem:
- I developed a form in Access from which the user could select multiple
plant records to print. This involved setting up a simple multi-selection
list box and then loading the criteria for a query using the example
shown in Microsoft support article Q135546 ("How to use a Multi-Select
List Box to Filter a Form") http://support.microsoft.com/default.aspx?scid=kb;en=us;Q135546.
This list box drives a query, which I will call "Qry:SelectedRecords" for the purpose of this discussion.
- I opened a new document using the Envelopes and Labels Wizard and set
up the document as if it were a page with 2 labels on it, each one 7" wide
x 5" high and each one with 1/2" margins on the left, top and
bottom.
- I added 3 textboxes to each of my labels: One for the text to be printed
(textbox1)one for the picture of the plant (textbox2)
and one for the logo to be printed in the lower right-hand corner (textbox3).
Each of these textboxes were setup without Lock
Anchor on and without Move With Text on. This allowed me to move the blocks
around until I had them placed just where I wanted.
- I set up a Mail Merge datasource using Qry:SelectedRecords for my data. In textbox 1 I inserted
all of the mail merge data fields I needed to construct the text part of
my sign. Each merge field was assigned its proper formatting.
- In textbox 2 I used Cindy Meister's (MS MVP) wonderful technique for
merging graphics from a database (see her website http://homepage.swissonline.net/cindymeister/MergFram.htm for
complete details) . This is essentially the code
I entered into textbox2:
{ INCLUDEPICTURE “c:\\tgppicture\\{MERGEFIELD
Picture }”}
where c:\\tgppictures\\ is the path where the pictures are
stored (note the double slashes which are required to make this work). The
field in my Access database which holds the name of the bitmap or jpg file
is the field being merged into this IncludePicture statement. I happened
to call that field PICTURE.
As Cindy explains on her website, once you merge the data to a new
document, you need to select all the textboxes and fields for update. Then
you can press ALT-F9 to see the pictures having
been loaded into the textboxes.
The same technique was used to load my Logo picture into the third
textbox.
I saved this document as SIGNS.DOC.
In my example, SIGNS.DOC looks like this:
- After merging to a new document the resulting document (LABEL1) looks
like this:
To get the picture to actually load into the document,
I had to manually select the text in LABEL1.DOC textbox2 and then do the Update
Fields function. At that point the picture would load and appear in the
correct box.
- Having figured out how to create the sign, it now became important to
automate the entire process so that the garden center managers could select
plants, press a button and have the signs show in print preview mode automatically.
I did not want them having to use the Update Fields function, the Merge
to New Document function or any other function besides Print and Close!
So I wrote a macro which I called "Startup" and stored in a module
in my initial merge document:
' startup Macro
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With
Application.Run MacroName:="MergeUpdate"
ActiveWindow.View.ShowFieldCodes = Not ActiveWindow.View.ShowFieldCodes
ActiveDocument.PrintPreview
This calls another macro, "MergeUpdate", after doing the
Merge to New Document. "MergeUpdate" performs the Update Fields
for all text boxes:
Sub MergeUpdate()
' MergeUpdate Macro
For Each s In ActiveDocument.Shapes
With s.TextFrame
If .HasText Then s.TextFrame.TextRange.Select
Selection.Fields.Update
End With
Next
Selection.WholeStory
Selection.Fields.Update
End Sub
The MergeUpdate macro must be stored in a global template or
in Normal.dot so that each and every time the merge creates a
new document and the new document becomes the "active document",
the MergeUpdate macro will still be available to run.
Once this is done, the Startup macro goes into Print Preview mode
so that the user sees the fully merged signs with pictures.
The Startup Macro is part of the original SIGNS.DOC's OnOpen event so that it runs as soon as the SIGNS.DOC is opened.
- The final step is creating a button which calls Word from the Access
and opens the Mail Merge document. The OnClick event for my button has
a variable "retval" defined and the following statement is called
after the datasource query is created:
retval = Shell("c:\program files\microsoft
office\office\winword.exe c:\tgpcatalog\signs.doc", vbNormalFocus)
To summarize: The user selects the plant(s) from the list box on the Access
form, presses the button and the program creates the query, "Qry:Selected Records".
Access then opens the Word document, SIGNS.DOC,
which uses "Qry:Selected Records" as its
datasource. Once this document is opened, the STARTUP macro in SIGNS.DOC merges it to a new document, (LABEL1
by default), and causes all the fields on that document to be updated and
the result to be previewed for printing.
This application is simple but has saved me a huge amount of grief. It has
made printing signs seamless to the users and has alleviated all of the
problems I had experienced using Access reports to print signs. The nursery
has saved a lot of money in production costs, because we can now print
our signs in house quickly and inexpensively.
About Diana Bodell, Guest Author
 I started my career in computers back when a big computer had 32K of memory,
fresh out of college in 1978. I spent the next 15 years as a systems
engineer, business software consultant for the manufacturing industry and
software designer with IBM, System Software Associates in Chicago and for
my own consulting/software firm, Gemma International.
I left corporate life
to slow down and be with my very young kids and then went back to work
in my favorite garden center, hoping to spend more time with my first love,
plants. Unfortunately the owners of The Growing Place in Naperville,
IL found out I had a systems background
and since they had wanted to implement a POS and back office system, I took
a raise in pay and left the plants for the back office. Things had changed
significantly since my days in programming and so I had to start over and
learn new tools and techniques including Access programming and VBA.
Helping
the nursery to automate their plant signs was probably the most interesting programming
challenge that I have faced so far. The nursery is currently converting
to new business software and a whole new network so I will be especially
challenged learning MSSQL and Crystal Reports as well this year.
On a personal
note, I live and work in the western suburbs of Chicago, am a mosaic
artist on the side when I am neither working nor tending to my three school-aged
children and wonderful husband (probably the best VB programmer I
have ever known). Oh, and I can never help mentioning that my father is a
Nobel laureate in physics (1988), although I got the other set of genes,
I think!
See Diana's web page for more info: http://www.brokenbackmosaics.com/.
|
