Changes to working code

[Greg Maxey]

I have some code that gets data from an Excel 2003 spreadsheet to populate a
listbox in a userform:


Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("E:\My Documents\Excel Files\DemoSpreadsheet.xls",
False, False, "Excel 8.0")
Set rs = db.OpenRecordset("SELECT * FROM `mySSRange`")
While Not rs.EOF
Me.ListBox1.AddItem rs.Fields(0).Value
rs.MoveNext
Wend
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

What do I need to do to modify this code to extract data from a Excel 2007
format file (xlsx or xlsm) and an Excel 2010 format file?

Thanks.

--
Greg Maxey

See my web site http://gregmaxey.mvps.org
for an eclectic collection of Word Tips.

Arrogance is a weed that grows mostly on a dunghill (Arabic proverb)

 

[JLGWhiz]

I suspect that changing these two elements of code to reflect the correct
file extension and Excel version would help.

Files\DemoSpreadsheet.xls",
False, False, "Excel 8.0")

 

[Greg Maxey]

I would have expected the same, but changing xls to xlsx or xlsm and
changing Excel 8.0 to Excel 12.0 generators errors. Either can not locate
an installable ISAM or the data is in the wrong format.

 

[JLGWhiz]

I am not that familiar with DAO interface but maybe this site will help:

http://support.microsoft.com/kb/246335

 

[Project Mangler]

Hi Greg,

I use 2003 and I know next to nothing about retrieving from .xls and .mdb
using either DAO or ADO but following your posting I used your code toplay
around with doing that until I got it working (in 2003) so thanks for
providing the code for that.

During my searchung I came across this:

"If you are needing to retrieve the data from an Excel 2007 spreadsheet,
instead of the reference being to the "Microsoft DAO 3.51 (or 3.6) Object
Library, it needs to be to the Microsoft Office 12.0 Access database engine
Object Library and the code that follows in this article needs to be
modified as indicated."

at this site:

http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm

My Excel 2007 is on my daughter's laptop at university so I haven't been
able to test.

HTH

 

[Helmut Meukel]

Greg,

I may be wrong with this, but I suspect DAO will not work with
the new file format of Excel 2007 and Excel 2010.
IIRC, when installing Access or VB6 + DAO you get asked
which istallable ISAM drivers to install: dbase, excel, ...
and which ODBC drivers.
M$ has abandoned DAO many years ago, I don't think they
provided new drivers with Office 2007 and 2010 for the new file
formats.

You could switch to ADO, but why do you use an external engine
at all? You can easily use excel's native functions and objects to access
the data in the spreadsheet.
You are running your code from inside Excel, aren't you?
Or is this code in a VB6 program or from Access?

Helmut.