Load Data Into ListBox1

[ryguy7272]

I created a word document, with many DocVariables, that imports data from an
Excel worksheet. This document works great. When I double-click this
document to open it, I get a message that reads 'Opening this document will
run the following SQL command: SELECT * FROM 'List'', etc. This works fine.
I am now trying to do something similar with a new word document. I inserted
some DocVariables into the new word document this morning, and expected my
ListBox1 to show the data that is stored in the spreadsheet, but my ListBox1
is empty. Is there some special reference I need to create to get the source
data to show in ListBox1? When I hit Alt+F11, I see that ‘Reference to
Normal’ so I expected it to work, but it doesn’t appear to be working. Can
anyone offer a suggestion?

Regards,
Ryan---

 

[ryguy7272]

I just fiddled with it some more and got it to work. Not sure how though...
I think I loaded the date from ListBox1 and then saved it into the new
document and then closed the new document and then opened it...and it worked.
Not a very technical solution, I know. If anyone can elaborate I'd
appreciate it.

Regards,
Ryan--

 

[Shauna Kelly]

Hi Ryan

I'm not sure I understand what you mean about DocVariables. Are these
Document Properties, or Variables? And, how are they related to the Excel
file?

For what it's worth, the easiest way I know to populate a ListBox from Excel
is described at
Load a ListBox from a Named Range in Excel using DAO
http://www.word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm

Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[RyGuy]

My method is as follows:
1. On the Insert menu, click Field.
2. In the Categories box, select Document Automation.
3. In the Field names list, select DocVariable.
4. In the New Name box, under Field properties, type the name of the
document variable.
5. Click OK.

This stuff is documented pretty well here:
http://support.microsoft.com/kb/306281

Actually, I am still encountering issues. The data loads from Excel into
ListBox1, but only if I open a certain file first (this is the initial file
that I created to get this whole thing working). If I try to open one of my
new templates, ListBox1 is still empty. I used the code you provided in your
link, Shauna. I also use this code to get the data from ListBox1 into the
Word document:

Private Sub CommandButton1_Click()
ListBox1.BoundColumn = 1
ActiveDocument.Variables("First_Name").Value = ListBox1.Value

ListBox1.BoundColumn = 2
ActiveDocument.Variables("Last_Name").Value = ListBox1.Value

ListBox1.BoundColumn = 3
ActiveDocument.Variables("Title").Value = ListBox1.Value
'etc., etc., etc...
ActiveDocument.Fields.Update
UserForm1.Hide

End Sub

I just can't get the data into ListBox1 when I open any of my new templates.
I guess I'll just do a little more research on the web, and see if I can
find out what to do. If you think you know Shauna, please post back.

Thanks,
Ryan---

 

[Shauna Kelly]

Hi Ryan

Can you describe how your files are organized?

For example, what is "the initial file that I created to get this whole
thing working"? Is that a document or a template? And when you say "If I try
to open one of my new templates..." are you doing File > Open to open the
template, or are you using File > New to create a new document from the
template?

I assume that what you're trying to do is to have a user create a new
document based on your template, and that at some point a userform is
displayed, which has a list box, which is populated from an Excel range, and
then you store the user's choices in the Variables.

If that's the case, then you need to put all your code in the template (the
..dot file). Make sure you have no code related to this in your normal.dot
file.

Then use File > New and create a new file from your document.

Does that help?

Shauna

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[ryguy7272]

It’s like this… I have an Excel file called Contacts and I named a range,
which is called List. I reference this range and load all data into
ListBox1, then choose the row I want to insert into the word document, then
click the control button to complete the insert.


UserForm Code below:
Private Sub CommandButton1_Click()

ListBox1.BoundColumn = 1
ActiveDocument.Variables("FirstName").Value = ListBox1.Value

ListBox1.BoundColumn = 2
ActiveDocument.Variables("LastName").Value = ListBox1.Value

ListBox1.BoundColumn = 3
ActiveDocument.Variables("Company").Value = ListBox1.Value

' etc.
ActiveDocument.Fields.Update
UserForm1.Hide
End Sub


Sub Userform1_Initialize()
Dim i As Integer, Addressee As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("C:\Merge\Contacts.xls", False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `List`")
' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With
' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
'ListBox1.List = rs.GetRows(NoOfRecords) 'Transposed List
' Cleanup
rs.Close
db.Close
'AddressBlock = db
Set rs = Nothing
Set db = Nothing
End Sub


Module code below:
Sub Userform1_Initialize()
UserForm1.Show
End Sub
(this macro is run from a toolbar)


That’s pretty much all there is to it. It works fine in the original word
document in which the macros were created, but it won’t work if I try to open
ListBox1 from any other word documents that have been formatted with Insert >
Field > DocVariable. ListBox1 opens, but no data is displayed inside
ListBox1. However, if I open the original word document that DOES work, and
then open another word document formatted with DocVariables, and then run the
Userform1_Initialize() macro, these other word documents do work. Any
thoughts on this? All macros are stored in the Normal.dot template. I know
you said don’t do this, but this seems to be the only way it will work with
that one original document. Do I have to create a bunch of templates and put
those macros (above) in every single template and save as a .dot or some such
thing? Any thoughts on this?

Thanks,
Ryan---

 

[Doug Robbins - Word MVP]

You should create a template that has the docvariable fields in it and into
which you import the userform and the module, renaming the routine int the
module to Autonew()

Use the File>Export and File>Import items in the Visual Basic Editor to
export the form and module from where you have it now and to import it into
the new template.

--
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

 

[ryguy7272]

Thanks Doug! You helped me a few times before, and I am grateful for the
help, but now I am thoroughly confused. Are you saying that all code is
stored in the template with the docvariable fields? I tried this and Word
seems to be unable to identify any macros if these macros are not in the
Normal template. I am much more comfortable developing macros in Excel than
Word. Unlike Excel, which allows users to store code in the specific
workbook that a user is working with, I thought all Word macros were stored
in the Normal template, and then this template was references when the code
fires. I guess I'll have to do more research on this later today and
tomorrow. I will get this to work!!! I just don't know when that will be...

Regards,
Ryan--

 

[Doug Robbins - Word MVP]

When you rename the code in the Module to AutoNew() and it is located in the
template, when you select New from the File menu and then select the
template as the basis for the document that you want to create, the code in
the AutoNew() macro will be executed and the UserForm will be displayed.


--
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

 

[ryguy7272]

This is where I am right now...

I have one sub, called AutoNew, and the code is as follows:
Sub AutoNew()
UserForm1.Show
End Sub


In addition, I have one UserForm, called UserForm1, and the code as follows:
Private Sub CommandButton1_Click()

ListBox1.BoundColumn = 1
ActiveDocument.Variables("Broker_First_Name").Value = ListBox1.Value

ListBox1.BoundColumn = 2
ActiveDocument.Variables("Broker_Last_Name").Value = ListBox1.Value

ListBox1.BoundColumn = 3
ActiveDocument.Variables("Title").Value = ListBox1.Value

ListBox1.BoundColumn = 4
ActiveDocument.Variables("Broker_Co_Name").Value = ListBox1.Value

ListBox1.BoundColumn = 5
ActiveDocument.Variables("Address1").Value = ListBox1.Value

ListBox1.BoundColumn = 6
ActiveDocument.Variables("Address2").Value = ListBox1.Value

ListBox1.BoundColumn = 7
ActiveDocument.Variables("City").Value = ListBox1.Value

' etc.
ActiveDocument.Fields.Update
UserForm1.Hide
End Sub

Private Sub UserForm_Initialize()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Open the database
Set db = OpenDatabase("C:\Mail Merge\Source Data.xls", False, False,
"Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM `List`")

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub


When I try to run the code, I typically get a message that says, 'The macro
cannot be found or has been disabled because of your security settings.'
This is totally not true because even if security is set to 'low' that
message still pops up. So whatever... Ok, with some cajoling and a bit of
chicanery, I can...sometimes...get the code to fire, and when it does, it
makes me choose the path to the source data. I'm not sure what causes this
because the path is mapped; it is embedded right in the code. This is pretty
silly. I can’t see my colleagues clicking around on the network drives to
find the source file to do the import. Sigh... Maybe this will only be
resolved the old fashioned way...BRUTE FORCE. I’ll just try various things
until I find one thing that works. By the way, is there any documentation
out there on this stuff. I searched in several Word books and found nothing;
I have seen only tidbits of information on the web. If anyone has any more
insight, please share. I am out of ideas.

Ryan—

 

[Doug Robbins - Word MVP]

If you want to send me a copy of the template and the data source (source
data.xls), I will take a look at it.

--
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

 

[Doug Robbins - Word MVP]

The problem here turned out to be inconsistencies in the data in the Excel
spreadsheet.

Word seems to check about the first seven or eight rows of data to determine
what it is dealing with. If it finds an inconsistency it seems to throw a
bit of a hissy fit.


--
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