Populating Access Combo Box with Excel Worksheet Names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My form has one command button and one combo box.

When I click the command button, the FileDialog(msoFileDialogFilePicker) appears and I select a .xls file.

After selecting the .xls file, I would like the combo box on my form to be populated with the names of the Worksheets contained in that .xls file.

Does anyone have any ideas?
 
You'll need to use Automation to start EXCEL, open the workbook, loop
through the woeksheets and store their names in an array variable, and then
use that variable as the data source for a Value List.


--

Ken Snell
<MS ACCESS MVP>

Kruegy Man said:
My form has one command button and one combo box.

When I click the command button, the FileDialog(msoFileDialogFilePicker)
Click to expand...
appears and I select a .xls file.
After selecting the .xls file, I would like the combo box on my form to be
Click to expand...
populated with the names of the Worksheets contained in that .xls file.
 
You'll need to use Automation to start EXCEL, open the workbook, loop
through the woeksheets and store their names in an array variable, and then
use that variable as the data source for a Value List.
Click to expand...

There's no need to open the workbook and doing so it relatively slow.
Much faster to use ADO's OpenSchema method to return all Excel tables
and eliminate the defined Names ('named ranges'). Here's a link to
some code:

http://groups.google.com/[email protected]

Jamie.

--
 
You'll need to use Automation to start EXCEL, open the workbook, loop
through the woeksheets and store their names in an array variable, and then
use that variable as the data source for a Value List.

Ken Snell
<MS ACCESS MVP>
Click to expand...



Thank you Ken! You led me to the new world of Automation and referencing Type Libraries! I knew I wanted to use Excel objects in access but I didn't know how... so maybe that should have been my question.

Anyway, after I referenced the Excel Type Library (Tools --> References) I could create an instance:

Dim appXL As New Excel.Application

of the Excel application and use its members! YEAH!!!!
You'll need to use Automation to start EXCEL, open the workbook,
Click to expand...

There is good news here, Excel doesn't actually need to be open to work with the workbook object.

Thanks Ken... this stuff rules!
 
Glad it's working! Good luck.

--

Ken Snell
<MS ACCESS MVP>

Kruegy Man said:
Thank you Ken! You led me to the new world of Automation and referencing
Click to expand...
Type Libraries! I knew I wanted to use Excel objects in access but I didn't
know how... so maybe that should have been my question.
 
Back
Top