From Word, need to populate list or combobox from Excel SS
Hi Mark,
I just tested the code from that article and it works fine for me, loading
ListBox1 with the data from a range that I named AccountCodes in an Excel
Worksheet named Accounts in the H:\Access97 folder on my PC.
Here is the code that I used:
Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
' Open the database
Set db = OpenDatabase("h:\Access97\Accounts.xls", False, False, "Excel
8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM AccountCodes")
' 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
I note that the code on the web page has myDatabase surrounded by a pair of
` i.e. `myDataBase` I thought that you may have included them and that is
what's causing your problem, but I have just tried my code again with the
following command
Set rs = db.OpenRecordset("SELECT * FROM `AccountCodes`")
instead of the corresponding line in the code above, and it works fine. In
other words, the `` appear to be ignored by VBA which I admit does surprise
me. Did you however use to apostropes instead of that symbol whatever it
is. If you use apostrophes, you will get a Run-time error '3450': Syntax
eror in query. Incomplete query clause.
If this information doesn't help you overcome your problem, paste the code
from your Iitialize() event into a message that you post back to the ng so
that we can see what you have.
Please respond to the newsgroups for the benefit of others who may be
interested.
Hope this helps
Doug Robbins - Word MVP
Quote:
> Thanks Doug
> Yes, I've been working from this very code example for a couple of hours
> now. For some reason, I can't get the listbox to populate. I've tried DAO
> 3.51 and 3.6.
> I've implemented the example almost verbatim (except for the path and
> filename)
> Any suggestions?
> Regards,
> Mark
> > Hi Mark,
> > See the article "Load a ListBox from a Named Range in Excel using DAO"
at:
> > http://www.mvps.org/word/FAQs/InterDev/FillListBoxFromXLDAO.htm
> > Please respond to the newsgroups for the benefit of others who may be
> > interested.
> > Hope this helps
> > Doug Robbins - Word MVP
> > > In Word XP, I need a simple way to populate a list box with an Excel
SS.
> I
> > > would like to link to all of the other data behind the SS after a
record
> > is
> > > selected.
> > > Any Ideas?
> > > Regards,
> > > Mark
