Import an Excel file choosing before the sheet or the named range

[ricky99]

Hello everyone,
I would like to import in Access an Excel 2007 file. The problem comes because it has:
- several sheets;
- every sheets with one (or more in some cases) named ranges.
The user, by an advanced form, import in a table not only the desidered Excel file (by FileDialogPicker), but the specific named range. In other words, I need to build a form where the user can select an Excel file, then choose from a list of sheets or named ranges contained in it.
What solutions can you propose me?
Many thanks for your attention and help.
Bye.



Riccardo

[June7]

So you need to dynamically build a RowSource list in a combobox of the sheet names and/or ranges in a selected workbook?

Try something like:

Dim objExc As Excel.Application
Dim objWbk As Excel.Workbook
Dim objWsh As Excel.Worksheet

Set objExc = CreateObject("Excel.Application")
objExc.Visible = True
Set objWbk = objExc.Workbooks.Add
For Each objWsh In objWbk.Worksheets
List1.AddItem objWsh.Name
Next

[ricky99]

Hello & thanks for you quick reply.
Just few notes to you answer.
Does the code need to open the application (Excel) to know its properties (named ranges, worksheets and so)? Ask that because the file could be quite biggy in terms of size and the procedure may take some seconds to complete.
Bye.

[June7]

As far as I know, yes Access must 'open' the Excel as an object that the code can manage. Once you are done retrieving the info, need to close the workbook and the Excel app. Review this thread http://forums.aspfree.com/microsoft-...el-413629.html