Creating MSWord labels from within Access with label-edit possibility

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
=====================
AlwinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RichardCorrieCommented:
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
svenkarlsenCommented:
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
AlwinAuthor Commented:
Hi Sven,

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

Thanks anyway
Aklin
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

svenkarlsenCommented:
Ok Aklin, - did I solve your problem or do you need further assistance ?

Regards,
Sven
svenkarlsenCommented:
If I did solve it, please remember to accept the answer ;-)

Regards,
Sven
AlwinAuthor Commented:
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')
svenkarlsenCommented:
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.
AlwinAuthor Commented:
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
svenkarlsenCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlwinAuthor Commented:
Hi Sven,

"Use  wdMailingLabels instead of wdFormLetters"

That did the trick!

Thanks a lot!

Alwin
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.