Code to populate listbox is not working

[Jade]

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

 

[Doug Robbins - Word MVP]

Are you sure that the named range TblRng exists in the spreadsheet in your
office?

--
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

 

[Jade]

Are you sure that the named range TblRng exists in the spreadsheet in your
office?

--
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















- Show quoted text -

Yes, it's a range. I'm at a loss as to why it's not working.

 

[Jade]

Are you sure that the named range TblRng exists in the spreadsheet in your
office?

--
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















- Show quoted text -

yes checked range...i'm at a loss as to why it's not working

 

[Shauna Kelly]

Hi Jade

You'll have to work through your code line by line and check that each line
has done what is expected of it. For example, is db correctly getting the
database? Is rs correctly getting the record set etc?

If the problem is that NumRecords is correct, but you're not retrieving that
many items in the list box, then I suggest that you go searching for a
problem in the data. Look for anything that's "wrong" - cells with the wrong
format (text instead of a number?), empty cells that shouldn't be empty,
more filled cells on one row than others, that kind of thing.

Hope this helps.

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