I'm trying to create a simple way for our church's computer-lightweight Music Librarian to print custom labels to go on the edge of the boxes that store each set of 50 pieces of sheet music. This is an XP, Word 2007 environment.



I want to configure a way to open Word, and feed it switches or a configuration file that instructs it to open an already-defined custom template, applying an already-formatted custom format of 5-across, 2-down labels, then opens and reads a statically-named Excel input file, and prints the labels.

A word about the "already-defined custom template:" the Avery labels used is their #5163 (2-across 4", 5-down 2"), but I need to print them in a rotated orientation of 5-across 2", 2-down 4". Avery Support (www.avery.com, 888/835-8379) was EXTRAORDINARILY HELPFUL in helping me design a Custom format that prints the #5163 labels in LANDSCAPE, with all the precise label margin, spacings & dimensions; then led me through each Word step in how to configure to print in those labels. This worked perfectly, for me, but our Music Librarian won't be able to pursue the steps.

So, I'll show the librarian how to create the needed records in a spreadsheet file, saving it with an appropriate content-indicative name (the labels are serialized), but also saving it with the static name the Word program will always open, like "Labels.xlsx". Then I hope to set up a Desktop shortcut that will invoke this special Word program that knows which template and input file to use to print the labels .. hopefully without any (or minimal) additional user input. I know about appending switches to the program invocation line of a Desktop Shortcut, but don't know which to use to tell the Word program what to do.

If Word cannot be invoked in such a manner, can you suggest some other mail-merge capable product that can be used this way?

Help me, Oh Wee Bon, Kinobe?

OK, I just discovered the answer to my own question: I need to record a macro to do all the work I outlined. Doh!

My question now is: can a switch specifying the name of the macro it is to execute be appended to the end of the command line in a Desktop shortcut that invokes Word? Will Word open and then run the specified macro?

Yes. See discussion of the /m switch at: http://support.microsoft.com/kb/210565

However, you might do better just to add a Document_Open macro containing the required code to the document's 'ThisDocument' module. That way, only the particular document is affected, rather than the whole Word session.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks, MacroPaul. Your sugg to add a Document_Open macro sounds feasible, but I don't yet know enough about writing macros to accomplish that .. I know only how to record what the macro recorder observes me doing. Lame, I know, I'm just not there yet.

Here's what I've managed to get a recorded macro to do .. but it needs some work. The contents of the macro are in the attached file "Print_Labels().txt.


The whole idea with this invocation of Word is for the Desktop shortcut to invoke Word, telling it to open an already-written document that has the labels already formatted. The shortcut-referenced macro then opens a Recipient List (which is an Excel spreadsheet containing all of the addressees’ data), imports all the records into the formatted label pages, prints them, and then exits the program. The command line I’m using in the Desktop shortcut is:

"C:\Program Files (x86)\Microsoft Office\Office14\WINWORD.EXE" C:\Users\Administrator\Desktop\PrintLabels.docx /mPrint_Labels

[ A word about that path: I’m developing this procedure at home in my Win7 64bit Office 2010 32bit environment. I know that the path to WINWORD will be different when I migrate the project to the target machine: an XP 32bit Microsoft Office 2007 32bit environment. ]

When invoked, even though the macro I recorded did observe me entering/responding to all prompts all the way through to the last step before exiting the program (which was to turn off the recorder), the Desktop shortcut, and the instance of Word & the referenced macro (Print_Labels), produced these concerns:

SQL WARNING
This pop-up message came up immediately:

! Opening this document will run the following SQL command:
SELECT * FROM ‘Labels$’
Data from your database will be placed in the document. Do you want to continue?
[ Yes ] [ No ] [ Help ]

The answer to the question is always [ Yes ], and I did click that button when I was recording the macro. How can I get the macro to assert the [ Yes ] answer at this point and not pause, awaiting the operator to click the [ Yes ] button? Is the answer-file technique used in some other applications appropriate here?

PRINT MARGINS WARNING
As it was getting ready to print the label pages, this warning came up:

? The margins of sections 1, 2, 3 are set outside the printable area of the page. Do you want to continue printing?
[ Yes ] [ No ] [ Help ]

The answer to the question is always [ Yes ], and I did click that button when I was recording the macro. (I saw the online Help advice to anticipate & ignore this message when printing multiple-across labels.) Again: How can I get the macro to assert the [ Yes ] answer at this point and not pause, awaiting the operator to click the [ Yes ] button?

DOCUMENT SAVE POP-UPS
All of the remaining Print operations happen without further operator response, but two prompts for the operator’s wishes about saving changes to the two documents that were modified & created: No, neither is to be saved.

! Do you want to save changes you made to “Form Letters1”?
[ Save ] [ Don’t Save ] [ Cancel ]

-and-

! Do you want to save changes you made to “PrintLabels.docx”?
[ Save ] [ Don’t Save ] [ Cancel ]

Again the appropriate responses (I want the macro to respond to) are: [ Don’t Save ], because all I want to happen is to print the labels; this set won’t need to be printed again.

PROGRAM EXIT
The screen reverts to a blank document, and the last thing the macro recorder saw me do was to stop recording the macro. I would now like for Word to exit, but the macro is no longer in charge. How to get it to exit now?

A WORD ABOUT THE ENVIRONMENT
I’m developing this in a Parallels-managed virtual PC Win7 64bit Office 2010 32bit environment on an Apple iMac. The printer is an Epson CX4200, and the virtual PC connectivity to it is working fine. I mention this because you may see code in the macro that refers to this convoluted development platform. Once I get it working in this environment, I hope to make the required changes necessary to get it to function properly in the target environment: a real PC running XP 32bit & Microsoft Office 2007 32bit. ]

THE MACRO
The macro that was recorded is attached. I have very little experience with writing/editing macros. Maybe you can suggest how to modify it to achieve the desired results?

Attached Files

Print_Labels().txt (1.6 KB, 7 views)

Hi jdorin,

If you're opening the document via a desktop shortcut, you really don't need a macro for anything more than replicating whatever parts of "each Word step in how to configure to print in those labels" need to be done each time you open the document, including selecting the output printer. However, none of that is in the code you posted. Everything else - labels, mergefields, etc - should already be present in the document. Automating the actual merge execution, printing and closure of the mailmerge main document is also quite feasible. See below.

The SQL warning is a standard message for a mailmerge document. The only way you can not have that warning is to drive the mailmerge setup via a macro from a document that, although it contains the labels & mergefields, isn't saved as a mailmerge main document. Although the necessary code to do this is in the macro you recorded, embedding all the complexity of connecting to the data source and running the data query in the macro actually makes it harder for the 'typical' user to do maintain the setup if, say, the Excel workbook is renamed or moved to a new folder - or you move between PCs and Macs.

The print margins warning suggests the printer configuration and/or page setup isn't quite right, even though you're apparently getting the desired output. Depending on the printer driver you're using, you may be able to reduce its non-printing zones around the page edges (not something you can do with many (if not all) of the standard MS driver offerings). Get the zones small enough, or increase the page margins sufficiently, and the message should go away. Even so, code below suppresses the message.

The first save prompt concerns your output document. If you send the output direct to the printer, instead of to a new document, the prompt for that document won't occur. Sending direct to the printer is the approach the macro takes.

The second save prompt concerns your mailmerge main document. That message can be suppressed, which the macro also does.

And, when it's finished processing, the macro quits Word (if there are no other documents open). On that front, if Word is already running when the shortcut for the mailmerge is used, I expect it'll simply open the document in the existing Word session. In that case, if there are other unsaved documents open, the user will get the usual 'save before closing/cancel message' - from which they'll want to cancel.

Regarding the environment, the code should work on any PC (not sure about Macs). Since I don't have the code that would be required to select your Epson CX4200, the code includes the ability to choose a printer at run-time (again, a bit more flexible if the printer's network name, for example, changes).

Note: As yet, the code doesn't close/exit - the lines to do that are commented out, so you can test the code before committing to it. For testing purposes, you might also want to save on page, which you can do by changing:
.Destination = wdSendToPrinter
to:
.Destination = wdSendToNewDocument

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

MacroPaul -

After several false starts with this project, I've concluded that my original design was too general and restrictive for the user. My thrust now with this is to use a couple of label template Word docs I've created, one for 8-labels per (Avery) page, and another for 10-labels, using a different blank label stock.

My initial approach denied the user the opportunity of a final edit of the labels prior to printing them - what was in the db was what would print - warts and all.

Late into development of the project, I learned that sometimes there is a need to print multiple copies of the same label, along with single prints of the others. How to address this possibility is the main thing that has caused me to step back and redesign the project.

And the approach I'm now pursuing is to create a How To Print Labels document for the user:

If you're printing Single Labels, the db itself becomes the input file.
If you're printing Multiple Labels -

• Extract from the db to a spreadsheet just the records you want to print.
• Edit the spreadsheet to create multiples (this will be the input file to the label-printer).

Click the appropriate 8- or 10-labels per page Desktop shortcut, bringing up the correct template.
Select the appropriate input file and recipients.
Edit the labels in the label-formatted file being sent to the printer.
Print them.
Voilà!

Done.

So, no macros, no subroutines - just a clear, hand-holding document for the user. Let's see if I can make it simple and clear enough for even our music librarian ingénue.

Thanks again for your help, Paul.

MacroPaul-
When opening one of the Word templates that accesses the MS Access db, I still encounter the warning about running a SQL command to access the db. Someone, I thought it was you, suggested making a registry change to disable that warning. I applied it on my development W7/Office 2010 platform and it works grandly. I now need to make the similar registry change on the target XP/Office 2007 platform, but cannot find the registry editing instructions. Did you provide that? Can you provide that again?

Thank you -
jdorin

Hi jdorin,

It wasn't me who suggested the registry change; it's also not a change I'd be inclined to suggest as it affects all mailmerge documents and can result in your data source being unwittingly tied up and preventing update access by other users. Whatever the change was (presumably one of those outlined at http://support.microsoft.com/kb/825765), it would entail modifying the equivalent keys on the other platforms - note that it's different for every Word version!

My approach would be to use a macro that keeps your mailmerge main document as an ordinary document and runs the SQL query from the code. For such an implementation, see: http://answers.microsoft.com/en-us/o...0-91114d2c896e. If you store the document in a 'trusted location', it won't generate the macro warning, either.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]