Labels

[bravofoxtrotuk]

Hi,

I'm from the world of Access (Office 2003), and though I know Access VBA(!),
and I've been looking on the forums for some while, I'm struggling with what
is probably very simple.

From Access I'm wanting to open a new Word document, and I want to then open
the Envelopes and Labels dialog box. I can test this in Word VBA using:

Dialogs(wdDialogToolsEnvelopesAndLabels).Show

- but how should I do all this from Access?

I then want to force an address into the 'address' box of the dialog with
text passed forward from Access (or from the clipboard). Is this possible?

The user would then select the type of label for printing a full page of the
same label.

(I'm doing this to get round the unfriendly way Access deals with labels.)

If someone could point me in the right direction...

Bob

 

[Doug Robbins - Word MVP]

Not really that difficult to do in Access.

1. create a new table containing as many records as there are labels on a
sheet (it does not matter what is in this table)

2. Create a select query based on the table that contains your label data
but to which you add the table created in 1 WITHOUT linking it in any way to
the table containing the label data

3. The result of running this query will be that each record in the table
that contains the label data will be repeated as many times as there are
labels on the sheet.

4. You can set a criteria to determine which of the record(s) from the
table containing the label data is/are included in the result of the query.

5. Use this query as the basis for a Label type report in Access.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[bravofoxtrotuk]

Hi Doug, thanks for reply.

I'm already doing that in Access, exactly as you say. The problem is it
ties my users to the label type I specify in the report, and I keep getting
asked for more different label types - which means yet another label type
report each time.

My idea was to pass the address I want repeated on each label into the
dialog box. The user can then choose whatever label type they want, to print
a whole sheet with the same address (Word being much more user-friendly in
this).

I'm doing similar with other labels users may want (but not multiple), and
all they need then do is copy a 'template' Word doc, and then choose the
label type they want in Word.

I have tried giving instructions on measuring the label, changing the
dimensions etc, but it's messy. As you will know, to change the label type
in Access you need to fiddle with report design - not something I want my
users to attempt.

Does that make sense?

Bob

 

[Doug Robbins - Word MVP]

I have not been able to find a way to populate the address field in the
Labels dialog, but the following code in the click event of a command button
on an Access Form that I put together displays the dialog so that the user
can selecct the label that they want to use and then when they click the New
Document button, it populates the (relevant) cells of the table in that
document with the address details from the record displayed in the Access
Form.

As this code uses Early Binding, it is necessary to set a reference to the
relevant Word Object Model. The version of Access used here was 2007, but
the only Word Object Model that was available was that for Word 2010 (Word
14), so it was Word 2010 that was activated by the code.

Private Sub cmdMakeLabel_Click()
'On Error GoTo Err_cmdMakeLabel_Click

Dim oWord As Word.Application
Dim WordWasNotRunning As Boolean
Dim oDoc As Word.Document
Dim labeldoc As Word.Document
Dim myDialog As Word.Dialog
Dim straddress As String
Dim i As Long, j As Long
Me.Company.SetFocus
straddress = Me.Company.Text
Me.Address1.SetFocus
straddress = straddress & vbCr & Me.Address1.Text
Me.Address2.SetFocus
straddress = straddress & vbCr & Me.Address2.Text

'Get existing instance of Word if it's open; otherwise create a new one

On Error Resume Next

Set oWord = GetObject(, "Word.Application")
If Err Then
Set oWord = New Word.Application
WordWasNotRunning = True
End If

On Error GoTo Err_cmdMakeLabel_Click

oWord.Visible = True
oWord.Activate
Set oDoc = oWord.Documents.Add
Set myDialog = oWord.Dialogs(wdDialogToolsEnvelopesAndLabels)
myDialog.Show
Set labeldoc = oWord.ActiveDocument
With labeldoc.Tables(1)
For i = 1 To .Rows.Count
For j = 1 To .Columns.Count
If .Cell(i, j).Width = .Cell(1, 1).Width Then
.Cell(i, j).Range.Text = straddress
End If
Next j
Next i
End With

If WordWasNotRunning Then
oWord.Quit
End If

Set oWord = Nothing
Set oDoc = Nothing
Set labeldoc = Nothing
Set myDialog = Nothing


Exit_cmdMakeLabel_Click:
Exit Sub

Err_cmdMakeLabel_Click:
MsgBox Err.Description
Resume Exit_cmdMakeLabel_Click

End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Graham Mayor]

You can populate the address field as follows:

Dim strAdd As String
strAdd = "Address to go in the address field"
With Dialogs(wdDialogToolsCreateLabels)
.addrtext = strAdd
.Show
End With

http://msdn.microsoft.com/en-us/library/aa211930(office.11).aspx

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Doug Robbins - Word MVP]

Making use of Graham's tip and also closing the blank document that is
opened:

Private Sub cmdMakeLabel_Click()

Dim oWord As Word.Application
Dim WordWasNotRunning As Boolean
Dim oDoc As Word.Document
Dim myDialog As Word.Dialog
Dim straddress As String
Dim i As Long, j As Long
Me.Company.SetFocus
straddress = Me.Company.Text
Me.Address1.SetFocus
straddress = straddress & vbCr & Me.Address1.Text
Me.Address2.SetFocus
straddress = straddress & vbCr & Me.Address2.Text
Me.City.SetFocus
straddress = straddress & vbCr & Me.City.Text

'Get existing instance of Word if it's open; otherwise create a new one

On Error Resume Next

Set oWord = GetObject(, "Word.Application")
If Err Then
Set oWord = New Word.Application
WordWasNotRunning = True
End If

On Error GoTo Err_cmdMakeLabel_Click

oWord.Visible = True
oWord.Activate
Set oDoc = oWord.Documents.Add
Set myDialog = oWord.Dialogs(wdDialogToolsCreateLabels)
With myDialog
.addrtext = straddress
.Show
End With
oDoc.Close

If WordWasNotRunning Then
oWord.Quit
End If

'Make sure you release object references.

Set oWord = Nothing
Set oDoc = Nothing
Set myDialog = Nothing


Exit_cmdMakeLabel_Click:
Exit Sub

Err_cmdMakeLabel_Click:
MsgBox Err.Description
Resume Exit_cmdMakeLabel_Click

End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[bravofoxtrotuk]

Guys,

thank you for all of that. Once someone points it out, it always seems
remarkably straight forward.

I've tried that code on Word/Access 2003 and it works fine - it's just a
pity it uses early binding. I've got users using all manner of different
versions, and I've used a lot of Albert Kallal's Word late binding code to
get round it. So I'm going to experiment to see if I can possibly get it to
work using late binding - though that will push my skills to the limit...

Thanks again,

Bob

 

[Doug Robbins - Word MVP]

It should certainly be possible to change it to use late binding though you
might have to revert to my earlier version rather than have the address
appear in the labels dialog as with late binding, you may come unstuck on
the .addrtext = straddress

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com