Solved

Creating MSWord labels from within Access with label-edit possibility

Posted on 2003-11-07
10
674 Views
Last Modified: 2007-12-19
Hi Experts,

I have the following problem:
From an Access database I want to print labels with MS Word.
The template file for the labels is called 'labels.dot'

Before the merge I want to be able to change the layout of the labels, i.e. remove or add fields.
When the msgbox is answered with 'yes' Word shows the labels layout, so I can remove fields or move fields up or down on the first label.
But the 'Propagate labels' button is grayed out and disabled, that means I cannot modify all the labels.
What am I doing wrong?

Thanks.
Alwin

The Code:

=====================

Public Function makeMyLabels(whichList)

Dim oMainDoc As Word.Document, wrdApp As Word.Application, wordDoc As String
Dim sDBPath, mySQL As String
Dim goMerge As Boolean

If MsgBox("Edit labels before merge?", _
    vbQuestion + vbYesNo + vbDefaultButton2, _
    "Merging labels") = vbYes Then
   
    goMerge = False
  Else
    goMerge = True
  End If


sDBPath = CurrentDb.name
mySQL = "SELECT * FROM " & whichList

On Error Resume Next
wordDoc = myDirectory & "labels.dot"
On Error Resume Next
Set wrdApp = GetObject(, "word.application")
If Err.Number <> 0 Then
   Set wrdApp = CreateObject("word.application")
End If

Set oMainDoc = wrdApp.Documents.Open(wordDoc)
With oMainDoc.MailMerge
    .MainDocumentType = wdFormLetters
   
    .OpenDataSource name:=sDBPath, _
           SQLStatement:="SELECT * FROM [" & whichList & "]"
End With

If Not goMerge Then
    GoTo doContinue
Else
    GoTo doMerge
End If
 
doMerge:
'merge to a new document
With oMainDoc
        .MailMerge.Destination = wdSendToNewDocument
        .MailMerge.Execute Pause:=False
        .Close SaveChanges:=wdDoNotSaveChanges
End With

doContinue:
wrdApp.Visible = True

Exit_makeMyLabels:
    Exit Function

Err_makeMyLabels:
    MsgBox Err.Description
    Resume Exit_makeMyLabels
   
End Function
=====================
0
Comment
Question by:Alwin
  • 5
  • 4
10 Comments
 
LVL 10

Expert Comment

by:RichardCorrie
ID: 9700763
try changing
Set oMainDoc = wrdApp.Documents.Open(wordDoc)
to
Set oMainDoc = wrdApp.Documents.add(Template:=wordDoc)

This creates a new document rather than editing your template, not a good idea if you are going multiuser

Later
Richard
0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9701745
When you get to Word in merge-mode, you can only edit the common design for all labels.

Do a merge to a new document, - then you can edit all individual labels in that document before printing.

Regards,
Sven
0
 

Author Comment

by:Alwin
ID: 9701802
Hi Sven,

That's just what I want, editing the design for all labels.
I am talking about 800 labels!

Thanks anyway
Aklin
0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9701826
Ok Aklin, - did I solve your problem or do you need further assistance ?

Regards,
Sven
0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9701862
If I did solve it, please remember to accept the answer ;-)

Regards,
Sven
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Alwin
ID: 9701901
Hi Sven,

No I have no solution yet.

I want to be able to modify the layout for ALL labels, so in merge-mode I want to modify label nr 1 and than click the 'propagate labels' button to change all.
But the 'Propagate labels' button is grayed out and disabled.

Thanks,
Alwin (formerly misspelled 'Aklin')
0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9702004
Alwin,
I'm not quite with you here: if you change the lay-out of that one label, you DO change the lay-out of all labels.

Have you tried this:

1. Open the merge in Word
2. Change the lay-out
3. Merge to a new file
4. View the result

In label-merge, you can only modify the lay-out of one label, which is used as a template for all labels.
0
 

Author Comment

by:Alwin
ID: 9702253
Sven,

Unfortunately not.
You change the first label and then click 'propagate labels'.
After that all the labels are being changed occording to the first label.
After that you can merge the lot.
When I open the merge file from Word, all this is possible, but from Access (see script) the propagate button is disabled. I don't know why.

By the way, I am useing Word 2002.
I don't know how it is in earlier versions.

Unfortunately I have to go now.
Have a nice weekend

Regards,
Alwin
0
 
LVL 9

Accepted Solution

by:
svenkarlsen earned 250 total points
ID: 9702561
Right, - sorry! (Still stuck with 2000 here at home ;-)


Anyhow, - the problem is in your code: you open the merge as a normal doc,not as a labels-doc:

With oMainDoc.MailMerge
    .MainDocumentType = wdFormLetters
                                      -------^-------    <<--
    .OpenDataSource name:=sDBPath, _
           SQLStatement:="SELECT * FROM [" & whichList & "]"
End With

Use  wdMailingLabels instead of wdFormLetters


Regards,
Sven
0
 

Author Comment

by:Alwin
ID: 9705963
Hi Sven,

"Use  wdMailingLabels instead of wdFormLetters"

That did the trick!

Thanks a lot!

Alwin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now