Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mailling Labels (VBA/MS Excel/97)

    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

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mailling Labels (VBA/MS Excel/97)

    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.
    <pre> Range("A100:A104").Copy
    Application.ActivateMicrosoftApp xlMicrosoftWord
    <font color=448800>' code to paste in MS Word</font color=448800></pre>

    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

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Mailling Labels (VBA/MS Excel/97)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •