Populating Access Combo Box with Excel Worksheet Names

[Kruegy Man]

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?

 

[Ken Snell]

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>

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.

 

[Jamie Collins]

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.

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.

--

 

[Kruegy Man]

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>

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,

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

Thanks Ken... this stuff rules!

 

[Ken Snell]

Glad it's working! Good luck.

--

Ken Snell
<MS ACCESS MVP>

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.