Code to populate listbox is not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jade - 22 Feb 2007 03:31 GMT

Hi,

I used the following code which worked fine a few days ago and now
doesn't pull the data from into the listbox.

I've checked the reference, syntax, and just can't understand why it
isn't working. I have it on my computer at home and it works fine yet
at work (where it worked until just recently) it doesn't.

Any suggestions as to why this is occuring ...or if someone has an
alternate method to pull data from a range in an excel sheet to a
listbox that would be great as well. Basically any help would be
great.

here it is:

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

Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel
8.0")
Set rs = db.OpenRecordset("SELECT * FROM TblRng")

With rs
.MoveLast
NumRecords = .RecordCount
.MoveFirst
End With

ListBox1.ColumnCount = rs.Fields.Count
ListBox1.Column = rs.GetRows(NumRecords)

rs.Close
db.Close

Set rs = Nothing
Set db=nothing

Thanks

Reply to this Message

Perry - 27 Feb 2007 12:25 GMT

This is not an answer to the "Why isn't it working question", this is an
alternative way
to get the MS Word listbox populated using ADO to get access to an Excel
file.
Set a reference to Microsoft ActiveX Data Objects library and use following
code
to populate a listbox in a Word Userform.

conditions:
Workbook name: C:\Test\Book1.xls
Substitute Sheet1 as sheet name in below snip, to read the the name of the
worksheet where TblRng is located.
If these settings are done, no other coding need to populate the listbox

Krgrds,
Perry

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test\Book1.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""

Set rs = cn.Execute("select * from [Sheet1$]")

With rs
Me.ListBox1.ColumnCount = .Fields.Count
Me.ListBox1.Column = .GetRows(.RecordCount)
End With

rs.close: Set rs = Nothing
cn.close: Set cn = Nothing

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

> Hi,
>
[quoted text clipped - 36 lines]
>
> Thanks

Reply to this Message

Perry - 27 Feb 2007 17:06 GMT

> Set rs = cn.Execute("select * from [Sheet1$]")

Above line from previously forwarded code, isn't even necessary
Replace the above line by following statement in same code

Set rs = cn.Execute("[Sheet1$]")
'will suffice ;-)

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

> This is not an answer to the "Why isn't it working question", this is an
> alternative way
[quoted text clipped - 78 lines]
>>
>> Thanks

Reply to this Message

Perry - 27 Feb 2007 12:37 GMT

Further to yr question:
If TblRng is a named range, surround named ranges by single quotes in yr
query statements (for Excel).

Replace this

> Set rs = db.OpenRecordset("SELECT * FROM TblRng")

by
Set rs = db.OpenRecordset("SELECT * FROM 'TblRng'")
(note the singlequotes around TblRng)

If this doesn't resolve it, you could use the sheet name where TblRng is
hosted
but then you will have to "marshall" the tablename as examplified below,
whereby
Sheet1 is the hostsheet of TblRng.

Set rs = db.OpenRecordset("SELECT * FROM [Sheet1$]")

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

> Hi,
>
[quoted text clipped - 36 lines]
>
> Thanks

Reply to this Message

Perry - 27 Feb 2007 17:15 GMT

Likewise, replace following line

> Set rs = db.OpenRecordset("SELECT * FROM 'TblRng'")

by
Set rs = db.OpenRecordset("TblRng")
(Provided, TblRng is a valid Named Range in yr Excel workbook)
If above doesn't work (which I doubt), below lines *will* work
Set rs = db.OpenRecordset("Sheet1$")
(Provided Sheet1 is the sheet where TblRng is hosted

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

> Further to yr question:
> If TblRng is a named range, surround named ranges by single quotes in yr
[quoted text clipped - 62 lines]
>>
>> Thanks

Reply to this Message