Solved

Creating MSWord labels from within Access with label-edit possibility

Posted on 2003-11-07
10
677 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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
 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 89
Ms Access VBA Variables 6 27
VBA Access 2016 syntax 6 43
Update a text value in another table 10 40
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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

773 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