Mailling Labels (VBA/MS Excel/97)

Topic

New Reply

In a project I am developing, I have a need to print a single mailling label from within MS Excel by having the user elect (via a userform) to print either the label or a report. Each user has a supply of blank labels that have 8 rows and 3 columns of peel and stick labels.

My plan, so far, is to create a userform that looks like the layout of the labels sheet with a textbox representing each label. The user will double-click on the label and text from several cells on one of the worksheets will be printed to the selected label position.

Each user could have one of 24 different label positions to print because of any earlier printing.

How can I tell Excel programmatically that I want to print the relevant label (with the cells’ values that incidentally come from 6 different cells) onto the said label in the correct position?

Any suggestions will be much appreciated and the end product will be posted here for anybody interested.

TIA Leigh

Reply | Quote

Replies

I now have this project at the stage where the data required for the label is temporarily stored in the last worksheet and then deleted when the user closes the workbook.

I used the following code to start MS Word after copying the required cells.

    Range("A100:A104").Copy
	Application.ActivateMicrosoftApp xlMicrosoftWord
	' code to paste in MS Word

Any suggestions as I have not determined a way of having control over MS Word to paste in the copied text from within the MS Excel macro.

I will be taking info from the particular label that the user selects in the userform so that MS Word’s Page Setup can be modified to suit the relevant label position.

Help! Leigh

Reply | Quote

The typical steps to automate Word include:

Dim wrdApp as Word.Application, wrdDoc as Word.Document

Then use GetObject and/or CreateObject to set a reference to Word. (You should be able to find several examples of this in the Lounge.)

Then create a reference to a document using something like:

wrdDoc = wrdApp.Documents.Add(Template:=MyTemplate.dot)

Let’s assume MyTemplate.dot contains the label layout, so all you have to do it specify, for example, that you want to paste to a particular cell.

Perhaps you already have the full solution by now…if so, please post it or any additional questions.

Reply | Quote