Listbox selections dumped to MS Word

[Pete via AccessMonster.com]

Hello, I'm new to Access and trying to get MS Word to open with the
selections of my listbox. I can multi select items in my listbox. But then
when I click on a button, I need to take those items selected and place them
in a word document, running continuous like the following with a dividing
line to separate the items:

Name1
address1
state1
memo1
______________________________________________
Name2
address2
state2
memo2

Where the Name1 group is the first of the multi selection in the listbox and
the Name2 group is the second selection in the listbox. The code will need
to be flexible so that no matter how many lines I select in the list box, it
will print them in MS Word.

I'm thinking that I must first add the mult selected to a table and then
output the table to Word. Not sure. there may be a simplier way.

The main things I really need help with is writing out the multi list; and
then writing the list to MS Word. If writing the mult list to MS Word is one
step, that's even better.

Thanks for your help,
Pete

 

[Kipp Woodard]

OK, I wrote this code to get you going. It uses late binding for Word. If
you want to use early binding for Word, reference the "Microsoft Word
<version> Object Library", comment-out Object versions of the Word variables,
and uncomment the early bound versions of the Word variables.

Here's the code:
================
Private Sub cmdOutputToWord_Click()
Const PROC_NAME As String = "cmdOutputToWord_Click"

' Dim oWordApplication As Word.application
Dim oWordApplication As Object
' Dim oWordDocument As Word.Document
Dim oWordDocument As Object
' Dim oParagraph As Paragraph
Dim oParagraph As Object
Dim vItem As Variant
Dim iColumns As Integer

On Error GoTo ErrorHandler

' Get Word going...
' Set oWordApplication = New Word.application
Set oWordApplication = CreateObject("Word.application")

' Get a new document.
Set oWordDocument = oWordApplication.Documents.Add

' Loop the selected items of the ListBox control.
For Each vItem In Me.lstUsers.ItemsSelected
' Get a new paragraph.
Set oParagraph = oWordDocument.Paragraphs.Add

' Loop for the number of columns of the ListBox control.
For iColumns = 0 To Me.lstUsers.ColumnCount - 1
With oParagraph.Range
' This line assumes you specified Yes for the list control's
[Column Heads] property.
' This version puts the Name of each column before the value.
.Text = .Text & Me.lstUsers.Column(iColumns, 0) & ": " &
Me.lstUsers.Column(iColumns, vItem)

' Use this line if you specified No for the list control's
[Column Heads] property.
' This version just puts the value of each column.
' .Text = .Text & Me.lstUsers.Column(iColumns, 0) & ": " &
Me.lstUsers.Column(iColumns, vItem)

' Force single spacing.
' .ParagraphFormat.LineSpacingRule = wdLineSpaceSingle
.ParagraphFormat.LineSpacingRule = 0
.ParagraphFormat.SpaceAfter = 0
End With
Next

' Insert an extra paragraph for spacing.
Set oParagraph = oWordDocument.Paragraphs.Add
Next

Cleanup:
' Let's see the document.
oWordDocument.Activate
oWordApplication.Visible = True

' Drop Word references.
Set oWordDocument = Nothing
Set oWordApplication = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , Me.NAME & "."
& PROC_NAME

On Error Resume Next

GoTo Cleanup

End Sub

================
End of code

 

[Pete via AccessMonster.com]

Kipp, thanks! this works perfect. The only thing is that for the memo1 lines
are memo type in Access and they can be large. Right now it is stopping at
around 40 words.

How can I get it to print all of the contents in that field?

Thanks,
Pete

OK, I wrote this code to get you going. It uses late binding for Word. If
you want to use early binding for Word, reference the "Microsoft Word
<version> Object Library", comment-out Object versions of the Word variables,
and uncomment the early bound versions of the Word variables.

Here's the code:
================
Private Sub cmdOutputToWord_Click()
Const PROC_NAME As String = "cmdOutputToWord_Click"

' Dim oWordApplication As Word.application
Dim oWordApplication As Object
' Dim oWordDocument As Word.Document
Dim oWordDocument As Object
' Dim oParagraph As Paragraph
Dim oParagraph As Object
Dim vItem As Variant
Dim iColumns As Integer

On Error GoTo ErrorHandler

' Get Word going...
' Set oWordApplication = New Word.application
Set oWordApplication = CreateObject("Word.application")

' Get a new document.
Set oWordDocument = oWordApplication.Documents.Add

' Loop the selected items of the ListBox control.
For Each vItem In Me.lstUsers.ItemsSelected
' Get a new paragraph.
Set oParagraph = oWordDocument.Paragraphs.Add

' Loop for the number of columns of the ListBox control.
For iColumns = 0 To Me.lstUsers.ColumnCount - 1
With oParagraph.Range
' This line assumes you specified Yes for the list control's
[Column Heads] property.
' This version puts the Name of each column before the value.
.Text = .Text & Me.lstUsers.Column(iColumns, 0) & ": " &
Me.lstUsers.Column(iColumns, vItem)

' Use this line if you specified No for the list control's
[Column Heads] property.
' This version just puts the value of each column.
' .Text = .Text & Me.lstUsers.Column(iColumns, 0) & ": " &
Me.lstUsers.Column(iColumns, vItem)

' Force single spacing.
' .ParagraphFormat.LineSpacingRule = wdLineSpaceSingle
.ParagraphFormat.LineSpacingRule = 0
.ParagraphFormat.SpaceAfter = 0
End With
Next

' Insert an extra paragraph for spacing.
Set oParagraph = oWordDocument.Paragraphs.Add
Next

Cleanup:
' Let's see the document.
oWordDocument.Activate
oWordApplication.Visible = True

' Drop Word references.
Set oWordDocument = Nothing
Set oWordApplication = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , Me.NAME & "."
& PROC_NAME

On Error Resume Next

GoTo Cleanup

End Sub

================
End of code

Hello, I'm new to Access and trying to get MS Word to open with the
selections of my listbox. I can multi select items in my listbox. But then

[quoted text clipped - 26 lines]

Thanks for your help,
Pete

 

[Kipp Woodard]

That limitation is probably coming from the fact that it is used in the
ListBox control. Am I right that the memo string is coming through the
ListBox?

If so, then I would make sure that one of the columns in the ListBox is the
key to the table that contains the memo field. Then I would use that get the
value of the memo column straight from the table while I'm building my Word
doc.

Glad to help with that if you need it.

Kipp, thanks! this works perfect. The only thing is that for the memo1 lines
are memo type in Access and they can be large. Right now it is stopping at
around 40 words.

How can I get it to print all of the contents in that field?

Thanks,
Pete

OK, I wrote this code to get you going. It uses late binding for Word. If
you want to use early binding for Word, reference the "Microsoft Word
<version> Object Library", comment-out Object versions of the Word variables,
and uncomment the early bound versions of the Word variables.

Here's the code:
================
Private Sub cmdOutputToWord_Click()
Const PROC_NAME As String = "cmdOutputToWord_Click"

' Dim oWordApplication As Word.application
Dim oWordApplication As Object
' Dim oWordDocument As Word.Document
Dim oWordDocument As Object
' Dim oParagraph As Paragraph
Dim oParagraph As Object
Dim vItem As Variant
Dim iColumns As Integer

On Error GoTo ErrorHandler

' Get Word going...
' Set oWordApplication = New Word.application
Set oWordApplication = CreateObject("Word.application")

' Get a new document.
Set oWordDocument = oWordApplication.Documents.Add

' Loop the selected items of the ListBox control.
For Each vItem In Me.lstUsers.ItemsSelected
' Get a new paragraph.
Set oParagraph = oWordDocument.Paragraphs.Add

' Loop for the number of columns of the ListBox control.
For iColumns = 0 To Me.lstUsers.ColumnCount - 1
With oParagraph.Range
' This line assumes you specified Yes for the list control's
[Column Heads] property.
' This version puts the Name of each column before the value.
.Text = .Text & Me.lstUsers.Column(iColumns, 0) & ": " &
Me.lstUsers.Column(iColumns, vItem)

' Use this line if you specified No for the list control's
[Column Heads] property.
' This version just puts the value of each column.
' .Text = .Text & Me.lstUsers.Column(iColumns, 0) & ": " &
Me.lstUsers.Column(iColumns, vItem)

' Force single spacing.
' .ParagraphFormat.LineSpacingRule = wdLineSpaceSingle
.ParagraphFormat.LineSpacingRule = 0
.ParagraphFormat.SpaceAfter = 0
End With
Next

' Insert an extra paragraph for spacing.
Set oParagraph = oWordDocument.Paragraphs.Add
Next

Cleanup:
' Let's see the document.
oWordDocument.Activate
oWordApplication.Visible = True

' Drop Word references.
Set oWordDocument = Nothing
Set oWordApplication = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , Me.NAME & "."
& PROC_NAME

On Error Resume Next

GoTo Cleanup

End Sub

================
End of code

Hello, I'm new to Access and trying to get MS Word to open with the
selections of my listbox. I can multi select items in my listbox. But then

[quoted text clipped - 26 lines]

Thanks for your help,
Pete

 

[Pete via AccessMonster.com]

Yes Kipp,
I'm not sure how to do that. I've added the key to the list box which is
called cntrlID. But not sure how to write from the table the memo field
while writing to Word. Please help.

Pete

That limitation is probably coming from the fact that it is used in the
ListBox control. Am I right that the memo string is coming through the
ListBox?

If so, then I would make sure that one of the columns in the ListBox is the
key to the table that contains the memo field. Then I would use that get the
value of the memo column straight from the table while I'm building my Word
doc.

Glad to help with that if you need it.

Kipp, thanks! this works perfect. The only thing is that for the memo1 lines
are memo type in Access and they can be large. Right now it is stopping at

[quoted text clipped - 93 lines]

 

[Kipp Woodard]

You can use the DFirst() function to get the value of the memo column from
the table.

In the example below, I use the DFirst() function to get the value of a memo
column named "CommentsMemo" from the table named "tblUser" based on the value
of the UserID key column of the table. The value of UserID is the first
column in my ListBox control.

oParagraph.Range.Text = oParagraph.Range.Text & "CommentsMemo: " &
Nz(DFirst("CommentsMemo", "tblUser", "UserID = """ & Me.lstUsers.Column(0,
vItem) & """"))

Yes Kipp,
I'm not sure how to do that. I've added the key to the list box which is
called cntrlID. But not sure how to write from the table the memo field
while writing to Word. Please help.

Pete

That limitation is probably coming from the fact that it is used in the
ListBox control. Am I right that the memo string is coming through the
ListBox?

If so, then I would make sure that one of the columns in the ListBox is the
key to the table that contains the memo field. Then I would use that get the
value of the memo column straight from the table while I'm building my Word
doc.

Glad to help with that if you need it.

Kipp, thanks! this works perfect. The only thing is that for the memo1 lines
are memo type in Access and they can be large. Right now it is stopping at

[quoted text clipped - 93 lines]

Thanks for your help,
Pete

 

[Pete via AccessMonster.com]

I add the code below, but it is giving me Error:3464, data type mismatch in
criteria expression.

All of the fields are text fields except the control key. Please help.

Thanks,
Pete

You can use the DFirst() function to get the value of the memo column from
the table.

In the example below, I use the DFirst() function to get the value of a memo
column named "CommentsMemo" from the table named "tblUser" based on the value
of the UserID key column of the table. The value of UserID is the first
column in my ListBox control.

oParagraph.Range.Text = oParagraph.Range.Text & "CommentsMemo: " &
Nz(DFirst("CommentsMemo", "tblUser", "UserID = """ & Me.lstUsers.Column(0,
vItem) & """"))

Yes Kipp,
I'm not sure how to do that. I've added the key to the list box which is

[quoted text clipped - 19 lines]

 

[Kipp Woodard]

In my example, UserID is a string. Since your key is a number, it would be
like this:

oParagraph.Range.Text = oParagraph.Range.Text & "CommentsMemo: " &
Nz(DFirst("CommentsMemo", "tblUser", "UserID = " & Me.lstUsers.Column(0,
vItem)))

The 0, in "Column(0, vItem)" is the column number in your listbox that
contains your key.

I add the code below, but it is giving me Error:3464, data type mismatch in
criteria expression.

All of the fields are text fields except the control key. Please help.

Thanks,
Pete

You can use the DFirst() function to get the value of the memo column from
the table.

In the example below, I use the DFirst() function to get the value of a memo
column named "CommentsMemo" from the table named "tblUser" based on the value
of the UserID key column of the table. The value of UserID is the first
column in my ListBox control.

oParagraph.Range.Text = oParagraph.Range.Text & "CommentsMemo: " &
Nz(DFirst("CommentsMemo", "tblUser", "UserID = """ & Me.lstUsers.Column(0,
vItem) & """"))

Yes Kipp,
I'm not sure how to do that. I've added the key to the list box which is

[quoted text clipped - 19 lines]

Thanks for your help,
Pete

 

[Pete via AccessMonster.com]

Ok, got it. It's working to write out the full memo field. But, where is the
best place for it in the midst of the code?

Right now it is writing it over and over and over even if i click on one item
in the list box. It should only write out one record. Also, it is not
writing out all the fields from the listbox. I'm thinking it may be the
placement of the code. Not sure. Please help.

Thanks,

In my example, UserID is a string. Since your key is a number, it would be
like this:

oParagraph.Range.Text = oParagraph.Range.Text & "CommentsMemo: " &
Nz(DFirst("CommentsMemo", "tblUser", "UserID = " & Me.lstUsers.Column(0,
vItem)))

The 0, in "Column(0, vItem)" is the column number in your listbox that
contains your key.

I add the code below, but it is giving me Error:3464, data type mismatch in
criteria expression.

[quoted text clipped - 21 lines]

 

[Kipp Woodard]

If you post your code I'll take a look and see what I can figure out.

Ok, got it. It's working to write out the full memo field. But, where is the
best place for it in the midst of the code?

Right now it is writing it over and over and over even if i click on one item
in the list box. It should only write out one record. Also, it is not
writing out all the fields from the listbox. I'm thinking it may be the
placement of the code. Not sure. Please help.

Thanks,

In my example, UserID is a string. Since your key is a number, it would be
like this:

oParagraph.Range.Text = oParagraph.Range.Text & "CommentsMemo: " &
Nz(DFirst("CommentsMemo", "tblUser", "UserID = " & Me.lstUsers.Column(0,
vItem)))

The 0, in "Column(0, vItem)" is the column number in your listbox that
contains your key.

I add the code below, but it is giving me Error:3464, data type mismatch in
criteria expression.

[quoted text clipped - 21 lines]

Thanks for your help,
Pete

 

[Pete via AccessMonster.com]

Hi Kipp,
It's the same code above that you gave me on how to write to Word which works
fine but truncates the memo field. But when I add the code below it repeats
over and over even If I just click on one value in the listbox:


oParagraph.Range.Text = oParagraph.Range.Text & "CustMemo: " &
Nz(DFirst("CustMemo", "tblCust", "CntrID = """ & Me.Listbox1.Column(0,
vItem) & """"))

I'm thinking it is where I'm placing it within your code you gave me. Or
maybe this should replace a line of code. not sure.

If you post your code I'll take a look and see what I can figure out.

Ok, got it. It's working to write out the full memo field. But, where is the
best place for it in the midst of the code?

[quoted text clipped - 21 lines]

 

[Kipp Woodard]

Can you post the whole section of code, so I can see where it is placed
within the for loop?

Hi Kipp,
It's the same code above that you gave me on how to write to Word which works
fine but truncates the memo field. But when I add the code below it repeats
over and over even If I just click on one value in the listbox:


oParagraph.Range.Text = oParagraph.Range.Text & "CustMemo: " &
Nz(DFirst("CustMemo", "tblCust", "CntrID = """ & Me.Listbox1.Column(0,
vItem) & """"))

I'm thinking it is where I'm placing it within your code you gave me. Or
maybe this should replace a line of code. not sure.

If you post your code I'll take a look and see what I can figure out.

Ok, got it. It's working to write out the full memo field. But, where is the
best place for it in the midst of the code?

[quoted text clipped - 21 lines]

Thanks for your help,
Pete

 

[Pete via AccessMonster.com]

Kipp, heres the code.
Private Sub cmdOutputToWord_Click()
Const PROC_NAME As String = "cmdOutputToWord_Click"

' Dim oWordApplication As Word.application
Dim oWordApplication As Object
' Dim oWordDocument As Word.Document
Dim oWordDocument As Object
' Dim oParagraph As Paragraph
Dim oParagraph As Object
Dim vItem As Variant
Dim iColumns As Integer

On Error GoTo ErrorHandler

' Get Word going...
' Set oWordApplication = New Word.application
Set oWordApplication = CreateObject("Word.application")

' Get a new document.
Set oWordDocument = oWordApplication.Documents.Add

' Loop the selected items of the ListBox control.
For Each vItem In Me.ListBox1.ItemsSelected
' Get a new paragraph.

Set oParagraph = oWordDocument.Paragraphs.Add

' Loop for the number of columns of the ListBox control.
For iColumns = 0 To Me.ListBox1.ColumnCount - 1
'XXXXXXX adding new text here to control memo field
'oParagraph.range.Text = oParagraph.range.Text & "Firm_Note: " & Nz(DFirst
("Firm_Note", "Firm", "CntrID = """ & Me.ListBox1.Column(4, vItem) & """"))
'oParagraph.range.Text = oParagraph.range.Text & "Firm_Note: " & Nz(DFirst
("Firm_Note", "Firm", "CntrID = " & Me.ListBox1.Column(0, vItem)))

With oParagraph.range
oParagraph.range.Text = oParagraph.range.Text & "Firm_Note: " & Nz
(DFirst("Firm_Note", "Firm", "CntrID = " & Me.ListBox1.Column(0, vItem)))

' oParagraph.range.Text = oParagraph.range.Text & "Firm_Note: " & Nz(DFirst
("Firm_Note", "Firm", "CntrID = """ & Me.ListBox1.Column(4, vItem) & """"))

'oParagraph.range.Text = oParagraph.range.Text & "Firm_Note: " & Nz(DFirst
("Firm_Note", "Firm", "CntrID = " & Me.ListBox1.Column(0, vItem)))

'.Text = oParagraph.range.Text & "Firm_Note: " & Nz(DFirst("Firm_Note",
"Firm", "CntrID = """ & Me.ListBox1.Column(5, vItem) & """"))

' This line assumes you specified Yes for the list control's
'[Column Heads] property.
' This version puts the Name of each column before the value.
' .Text = .Text & Me.ListBox1.Column(iColumns, 0) & ": " & Me.
ListBox1.Column(iColumns, vItem)

' Use this line if you specified No for the list control's
'[Column Heads] property.
' This version just puts the value of each column.
' XXXXXXXXXX coment out below
.Text = .Text & Me.ListBox1.Column(iColumns, 0) & ": " & Me.
ListBox1.Column(iColumns, vItem)
'oParagraph.range.Text = oParagraph.range.Text & "Firm_Note: " & Nz(DFirst
("Firm_Note", "Firm", "CntrID = " & Me.ListBox1.Column(0, vItem)))

' Force single spacing.
' .ParagraphFormat.LineSpacingRule = wdLineSpaceSingle

.ParagraphFormat.LineSpacingRule = 0
.ParagraphFormat.SpaceAfter = 0
End With
Next

' Insert an extra paragraph for spacing.
Set oParagraph = oWordDocument.Paragraphs.Add
Next

Cleanup:
' Let's see the document.
oWordDocument.Activate
oWordApplication.Visible = True

' Drop Word references.
Set oWordDocument = Nothing
Set oWordApplication = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , Me.Name & "." &
PROC_NAME

On Error Resume Next

GoTo Cleanup

End Sub

Can you post the whole section of code, so I can see where it is placed
within the for loop?

Hi Kipp,
It's the same code above that you gave me on how to write to Word which works

[quoted text clipped - 16 lines]

 

[Pete via AccessMonster.com]

Ah Kipp, I figured it out. I placed it after the next and it works fine now.

Thanks for all your help and patience.

Kipp, heres the code.
Private Sub cmdOutputToWord_Click()
Const PROC_NAME As String = "cmdOutputToWord_Click"

' Dim oWordApplication As Word.application
Dim oWordApplication As Object
' Dim oWordDocument As Word.Document
Dim oWordDocument As Object
' Dim oParagraph As Paragraph
Dim oParagraph As Object
Dim vItem As Variant
Dim iColumns As Integer

On Error GoTo ErrorHandler

' Get Word going...
' Set oWordApplication = New Word.application
Set oWordApplication = CreateObject("Word.application")

' Get a new document.
Set oWordDocument = oWordApplication.Documents.Add

' Loop the selected items of the ListBox control.
For Each vItem In Me.ListBox1.ItemsSelected
' Get a new paragraph.

Set oParagraph = oWordDocument.Paragraphs.Add

' Loop for the number of columns of the ListBox control.
For iColumns = 0 To Me.ListBox1.ColumnCount - 1
'XXXXXXX adding new text here to control memo field
'oParagraph.range.Text = oParagraph.range.Text & "Firm_Note: " & Nz(DFirst
("Firm_Note", "Firm", "CntrID = """ & Me.ListBox1.Column(4, vItem) & """"))
'oParagraph.range.Text = oParagraph.range.Text & "Firm_Note: " & Nz(DFirst
("Firm_Note", "Firm", "CntrID = " & Me.ListBox1.Column(0, vItem)))

With oParagraph.range
oParagraph.range.Text = oParagraph.range.Text & "Firm_Note: " & Nz
(DFirst("Firm_Note", "Firm", "CntrID = " & Me.ListBox1.Column(0, vItem)))

' oParagraph.range.Text = oParagraph.range.Text & "Firm_Note: " & Nz(DFirst
("Firm_Note", "Firm", "CntrID = """ & Me.ListBox1.Column(4, vItem) & """"))

'oParagraph.range.Text = oParagraph.range.Text & "Firm_Note: " & Nz(DFirst
("Firm_Note", "Firm", "CntrID = " & Me.ListBox1.Column(0, vItem)))

'.Text = oParagraph.range.Text & "Firm_Note: " & Nz(DFirst("Firm_Note",
"Firm", "CntrID = """ & Me.ListBox1.Column(5, vItem) & """"))

' This line assumes you specified Yes for the list control's
'[Column Heads] property.
' This version puts the Name of each column before the value.
' .Text = .Text & Me.ListBox1.Column(iColumns, 0) & ": " & Me.
ListBox1.Column(iColumns, vItem)

' Use this line if you specified No for the list control's
'[Column Heads] property.
' This version just puts the value of each column.
' XXXXXXXXXX coment out below
.Text = .Text & Me.ListBox1.Column(iColumns, 0) & ": " & Me.
ListBox1.Column(iColumns, vItem)
'oParagraph.range.Text = oParagraph.range.Text & "Firm_Note: " & Nz(DFirst
("Firm_Note", "Firm", "CntrID = " & Me.ListBox1.Column(0, vItem)))

' Force single spacing.
' .ParagraphFormat.LineSpacingRule = wdLineSpaceSingle

.ParagraphFormat.LineSpacingRule = 0
.ParagraphFormat.SpaceAfter = 0
End With
Next

' Insert an extra paragraph for spacing.
Set oParagraph = oWordDocument.Paragraphs.Add
Next

Cleanup:
' Let's see the document.
oWordDocument.Activate
oWordApplication.Visible = True

' Drop Word references.
Set oWordDocument = Nothing
Set oWordApplication = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , Me.Name & "." &
PROC_NAME

On Error Resume Next

GoTo Cleanup

End Sub

Can you post the whole section of code, so I can see where it is placed
within the for loop?

[quoted text clipped - 4 lines]

 

[Pete via AccessMonster.com]

Hi Kipp,

How can I bold the colum names in this code? For instance the firm_id is
writing out to Word but I would like it to write out bold. Is this possible?

Thanks,
Pete

Can you post the whole section of code, so I can see where it is placed
within the for loop?

Hi Kipp,
It's the same code above that you gave me on how to write to Word which works

[quoted text clipped - 16 lines]