Need to create multiple word documents from ListBox MultiSelect

[cys052571]

Hi,

I created a form with a ListBox and a Command Button. The users selects the values in the listbox and then click the button to create word documents. I've written VBA code to accomplish this. But it's not working properly. It opens multiple word documents but all for the same one. I'm new to VBA. Any help will be greatly appreciated!

Private Sub Command6_Click()

Dim appWord As Object
Dim varItem As Variant
Dim strPathToTemplateFile As String
Dim strPathToProspectiveFile As String
Dim strPreferredFileName As String

For Each varItem In Me.List0.ItemsSelected

strPathToTemplateFile = "C:\Temp\test.docx"
Set appWord = CreateObject("Word.Application")
appWord.Documents.Add strPathToTemplateFile 'opens the template as Document1
appWord.Visible = True

'Replace bookmarks with record data

With appWord.ActiveDocument.Bookmarks
![SchoolName].Range.Text = Me.Survey_Results_Q1.Form!SchoolName
![Date].Range.Text = Me.Survey_Results_Q1.Form!Date
![Q1a1Per].Range.Text = Format(Me.Survey_Results_Q1.Form!Q1a1Per, "#0.00%")
![Q1a2Per].Range.Text = Format(Me.Survey_Results_Q1.Form!Q1a2Per, "#0.00%")
![Q1a3Per].Range.Text = Format(Me.Survey_Results_Q1.Form!Q1a3Per, "#0.00%")
![Q1a4Per].Range.Text = Format(Me.Survey_Results_Q1.Form!Q1a4Per, "#0.00%")
![Q1b1Per].Range.Text = Format(Me.Survey_Results_Q1.Form!Q1b1Per, "#0.00%")
![Q1b2Per].Range.Text = Format(Me.Survey_Results_Q1.Form!Q1b2Per, "#0.00%")
![Q1b3Per].Range.Text = Format(Me.Survey_Results_Q1.Form!Q1b3Per, "#0.00%")
![Q1b4Per].Range.Text = Format(Me.Survey_Results_Q1.Form!Q1b4Per, "#0.00%")
![Q1c1Per].Range.Text = Format(Me.Survey_Results_Q1.Form!Q1c1Per, "#0.00%")
![Q1c2Per].Range.Text = Format(Me.Survey_Results_Q1.Form!Q1c2Per, "#0.00%")
![Q1c3Per].Range.Text = Format(Me.Survey_Results_Q1.Form!Q1c3Per, "#0.00%")
![Q1c4Per].Range.Text = Format(Me.Survey_Results_Q1.Form!Q1c4Per, "#0.00%")

End With
Next varItem

 

[Mihail]

I can't see any reference to yours List0 control after the instruction For Each varItem In Me.List0.ItemsSelected
What is supposed to happen when the list value is change from item to item ?

 

[cys052571]

I can't see any reference to yours List0 control after the instruction For Each varItem In Me.List0.ItemsSelected
What is supposed to happen when the list value is change from item to item ?

It's supposed to populate the bookmarks on the word documents with the value of the textbox on the form.I know I'm missing something. I just don't know what is missing.

 

[JHB]

... It opens multiple word documents but all for the same one.

Because you have placed the following inside the loop.

For Each varItem In Me.List0.ItemsSelected

strPathToTemplateFile = "C:\Temp\test.docx"
Set appWord = CreateObject("Word.Application")
appWord.Documents.Add strPathToTemplateFile 'opens the template as Document1
appWord.Visible = True

Place it outside:

..
strPathToTemplateFile = "C:\Temp\test.docx"
Set appWord = CreateObject("Word.Application")
appWord.Documents.Add strPathToTemplateFile 'opens the template as Document1
appWord.Visible = True

For Each varItem In Me.List0.ItemsSelected

'Replace bookmarks with record data
..

 

[Mihail]

@JHB
Your code will open only one word document.

The key should be to update something by using every varItem from Me.List0
This is what I don't see in the code.

 

[cys052571]

@JHB
Your code will open only one word document.

The key should be to update something by using every varItem from Me.List0
This is what I don't see in the code.

I understand what you are saying. But I don't know how to do it.

 

[Mihail]

If you can, upload the database (compatible with Access 2003 or 2007).
Or show us the row source for List0.

 

[JHB]

@JHB
Your code will open only one word document.

The key should be to update something by using every varItem from Me.List0
This is what I don't see in the code.

Hmm - I read it as the problem was that it opened multiple documents!

 

[cys052571]

If you can, upload the database (compatible with Access 2003 or 2007).
Or show us the row source for List0.

I just have a simple query for List0.

SchoolName Date Q1a Q1b Q1c
Shcool1 01/01/2013 1 3 1
School2 03/01/2013 2 1 3

 

[Mihail]

I've asked for the Row Source, not for the data.

 

[cys052571]

I've asked for the Row Source, not for the data.

Is this what you are looking for?

SELECT DISTINCT Survey_Results.SchoolName
FROM Survey_Results;

Sorry, I'm really new to VBA. Please be patient with me.

 

[Mihail]

Sorry, I'm really new to VBA. Please be patient with me.

Don't care. I am, indeed a little bit angry but regarding to myself :banghead: because I don't understand what happen here.
If you select only one item the code do the right job for that item ?

In first post you wrote:

But it's not working properly. It opens multiple word documents but all for the same one.

What mean this the same one ? For the first selected item ? Or is the same whatever you select ?

 

[cys052571]

Don't care. I am, indeed a little bit angry but regarding to myself :banghead: because I don't understand what happen here.
If you select only one item the code do the right job for that item ?

In first post you wrote:
What mean this the same one ? For the first selected item ? Or is the same whatever you select ?

If I select only one item, it doesn't pull the correct record into the word document.

Yes, when I select more than one items, it creates multiple documents for the first item selected.

 

[Mihail]

Try this:

For Each varItem In Me.List0.ItemsSelected
           List0 = varItem
   .............