Drop Down getting values from Excel

[Robert_L_Ross]

Ok, so I always get these questions from my co-workers on Friday afternoon...

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

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 spreadsheet, grabs the values in column A and enters those as
selections for the drop down box.

Any ideas?

 

[Shauna Kelly]

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]

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]

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]

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]

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