Re: Drop Down getting values from Excel

Hi Robert

1. The code must be the following, regardless of whether you're using Excel
10.0 or 11.0 (I confess I haven't tried it with 12):
Set db = OpenDatabase("C:\Work\Schools.xls", False, False, "Excel 8.0")

2. Your routine is named SchoolForm_Initialize(). That's an odd kind of name
for a Sub. Not wrong; just odd. Are you explicitly calling this routine?
That is, somewhere in, say, the UserForm_Initialize Sub do you explcitly
call SchoolForm_Initialize? The purpose of setting a break point is to run
the code and allow the code to stop at your break point. If it stops in the
middle of your routine, you know the routine is running. If you set a break
point in SchoolForm_Initialize and the code does not stop, then you know
that SchoolForm_Initialize is not, in fact, running at all. And that would
explain why the list box is empty. I suspect that SchoolForm_Initialize
isn't running at all.

Hope this helps.

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


"Robert_L_Ross" <RobertLRoss@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DE670C2E-5E38-4D7A-8280-74667BB4215C@xxxxxxxxxxxxxxxx
Shauna,

Well, this is all in a document template. I have a module1 that I have
this
code in:
Sub autonew()
UserForm1.Show
End Sub

As for putting a breakpoint...I'm not fully following. I can manually run
the code with a breakpoint at the "Set db = opendatabase("C:\Documents and
Settings\RRoss\Desktop\TESTSOURCE.xls", False, False, "Excel 8.0")" line,
no
earlier.

The form appears when I open a new document based on the template, but the
field is blank. No errors or anything either.

As for the Excel version...does it matter that I'm actually running Excel
10?

Thanks in advance!!


"Shauna Kelly" wrote:

Hi Robert

First, how and when is this code running? If you put a break on the first
line of the code, and then run your form, does your code actually run?

Second, this code
Set db = OpenDatabase("C:\Work\Schools.xls", False, False, "Excel
11.0")
should be
Set db = OpenDatabase("C:\Work\Schools.xls", False, False, "Excel
8.0")

Hope this helps.

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


"Robert_L_Ross" <RobertLRoss@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:61D5E9A1-514F-46DB-A091-8916ECFB8AB4@xxxxxxxxxxxxxxxx
Shauna,

Thanks for the link...it looks straightforward, but for some reason I'm
not
getting anything in my drop down box.

Here are my project specifics:
VB Version: 6.3
Office Version: 2003
Excel File: C:\Work\schools.xls
Named Range: School_Names A1:A201 (Cell A1 is the header)
Word File: C:\Work\SchoolTemplate.dot
Form Name: SchoolForm
Combo Box Name: SchoolField

Here's the code:

Private Sub SchoolForm_Initialize()

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

' Open the database
Set db = OpenDatabase("C:\Work\Schools.xls", False, False, "Excel
11.0")

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

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

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

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

' Cleanup
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Sub

The form loads, but the combo box won't populate. Any ideas what I'm
missing?

"Shauna Kelly" wrote:

Hi Robert

See
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


"Robert_L_Ross" <RobertLRoss@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:6EF1B246-4CDE-40EA-AEFD-4F9B42CE4FC0@xxxxxxxxxxxxxxxx
Ok, so I always get these questions from my co-workers on Friday
afternoon...

We want to establish a spread*** (let's call it schools.xls) that
has
school names (and maybe 3 or 4 other fields). The users would
populate
the
spread***, then go to a Word doc that has a drop down box that
feeds
off
of
the values in the spread***.

We know that standard drop down boxes only allow 25 entries, so I've
made
a
VB form that has a drop down box. I need to know how I can get code
that
links to the spread***, grabs the values in column A and enters
those
as
selections for the drop down box.

Any ideas?








.