Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 683
  • Last Modified:

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
=====================
0
Alwin
Asked:
Alwin
  • 5
  • 4
1 Solution
 
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
0
 
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
0
 
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

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

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

"Use  wdMailingLabels instead of wdFormLetters"

That did the trick!

Thanks a lot!

Alwin
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now