Changes to working code
in [Programming]
|
Prev: Insert a custom function in a cell by runnig a sub or through a macro.
Next: Insert a custom function in a cell by runnig a sub or through a ma
From: Greg Maxey on 14 May 2010 23:53 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)
From: JLGWhiz on 15 May 2010 10:31 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" <gmaxey(a)mIKEvICTORpAPAsIERRA.oSCARrOMEOgOLF> wrote in message news:ObFm$I%238KHA.4600(a)TK2MSFTNGP02.phx.gbl... >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) > > >
From: Greg Maxey on 15 May 2010 17:22 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 wrote: > 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" <gmaxey(a)mIKEvICTORpAPAsIERRA.oSCARrOMEOgOLF> wrote in > message news:ObFm$I%238KHA.4600(a)TK2MSFTNGP02.phx.gbl... >> 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)
From: JLGWhiz on 15 May 2010 23:43 I am not that familiar with DAO interface but maybe this site will help: http://support.microsoft.com/kb/246335 "Greg Maxey" <gmaxey(a)mIKEvICTORpAPAsIERRA.oSCARrOMEOgOLF> wrote in message news:erPyzSH9KHA.508(a)TK2MSFTNGP06.phx.gbl... >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 wrote: >> 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" <gmaxey(a)mIKEvICTORpAPAsIERRA.oSCARrOMEOgOLF> wrote in >> message news:ObFm$I%238KHA.4600(a)TK2MSFTNGP02.phx.gbl... >>> 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) > >
From: Project Mangler on 16 May 2010 04:04
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 "Greg Maxey" <gmaxey(a)mIKEvICTORpAPAsIERRA.oSCARrOMEOgOLF> wrote in message news:erPyzSH9KHA.508(a)TK2MSFTNGP06.phx.gbl... > 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 wrote: > > 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" <gmaxey(a)mIKEvICTORpAPAsIERRA.oSCARrOMEOgOLF> wrote in > > message news:ObFm$I%238KHA.4600(a)TK2MSFTNGP02.phx.gbl... > >> 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) > > |