Load a ListBox from a Named Range in Excel using DAO Article contributed by a nd updated for Office 2007 by You can retrieve data from a Named Range in an Excel spreadsheet, without opening the spreadsheet, by using DAO. The advantages of this, compared to automating Excel, is that it is much faster, and it does not require Excel to be installed on the user's machine. First, you need to set a reference in your project to the “Microsoft DAO 3.51 (or 3.6) Object Library”. To define a range in Excel, select the cells to be included in the range, then go Insert Name Define and type a name for the range. Note that the first row in the range is treated as a header row and is not retrieved.
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. The following example retrieves all the data from a range named “myDatabase” in an Excel file called “Book1.xls” located in folder “C: Test ”.
It then loads this data into a ListBox (ListBox1). The code is simplified by using the GetRows method. The thing to watch out for with the GetRows method is that the retrieved data array is transposed ie: the data in each record occupies one column in the array.
![Microsoft Microsoft](https://www.excel-easy.com/vba/examples/images/list-box/create-list-box.png)
As described [here](RowSource for ListBox does not work under Mac.
![Excel Excel](/uploads/1/2/5/4/125428873/469705186.jpg)
This is evident if you use: ListBox1.List = rs.GetRows(NoOfRecords) This is where the Column property comes in handy. If you assign the array to the Column property of the ListBox (as in the code below), the array is automatically transposed and the data appears in the correct layout in the ListBox.