Re: Load Data Into ListBox1

Tech-Archive recommends: Fix windows errors by optimizing your registry

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—




--
RyGuy


"Doug Robbins - Word MVP" wrote:

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" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B3210A91-84DA-4E70-B84F-DCD58C27A765@xxxxxxxxxxxxxxxx
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--

--
RyGuy


"Doug Robbins - Word MVP" wrote:

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" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0B283BF8-C7C3-4B28-8833-4EB71C8A78E6@xxxxxxxxxxxxxxxx
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---


--
RyGuy


"Shauna Kelly" wrote:

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





"RyGuy" <RyGuy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:94499D47-CA21-48B4-871A-18F352082F70@xxxxxxxxxxxxxxxx
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" wrote:

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


"ryguy7272" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:59411DB4-AB7C-4664-A6A4-1D05DBA2151A@xxxxxxxxxxxxxxxx
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--

.

Quantcast