Solved

Creating MSWord labels from within Access with label-edit possibility

Posted on 2003-11-07
10
680 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 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