Import Excel files choosing only a specific named range

[Richard1980]

Hello guys,
I need your appreciable help for my first question here. Unfortunately I am an Access newbie user.
I am developing a new tool to import and analyze financial data from an Excel DB, which is created in a quarterly basis with different releases (e.g. for the March 31st 2012 I could have three or four different releases).
This DB is usually created in an Excel file with 5 sheets, every sheet containing a specific named range for a group of cells.
Now, here comes the hard part (for me obviously ). I know already how to import the Excel file in an Access table, but I wish to allow the user to import a specific named range. It would be great if the user could choose with a combobox/listbox which named range to be imported.

With the code

Application.FileDialog(msoFileDialogFilePicker)

I open the browse window to choose the file and with this code:

DoCmd.TransferSpreadsheet acImport, , , , , db_IAS

I tell MS Access to import the file selected looking at "db_IAS", which is the name of cells range in first sheet. Clearly this method is not customizable enough, because the user cannot act on this.
I hope to be clear in my problem description and hope for your assistance.
Many thanks.
Bye.

Riccardo

 

[Trevor G]

This small amount of code will list the named ranges into a new worksheet, so you could adapt this from acces into a listbox or cbo or temp table that fills the named ranges and use that as your source.

Sub allNamedRanges()
Dim ws As Worksheet
Set ws = Worksheets.Add
ws.Range("A1").ListNames
End Sub

 

[Richard1980]

I have found a different and better solution: looping into the Names collection of the file and then return every named ranges in a combobox.
Only "problem" is that I use a command to open (by stealth mode, without seeing opening MS Excel) the workbook. This is not the best solution because if you have a big size Excel file the procedure is quite long. Infact, it has to open an Excel session and the file selected as well.
Is there another way to get Names colletion of the file without using this code:

.WoorkBooks.Open ("path&filename")

Bye. Thanks.

 

[vbaInet]

You must open the file to get any information about the workbook.

 

[Richard1980]

You must open the file to get any information about the workbook.

Just a question. Using an ADO connection I can get information about the file? I ask that because some users connect (by ADO or DAO as well) an Access DB to Excel to import worksheets or so.
In other words, is the only way to get worksheets/named ranges names by open the Excel file?

 

[vbaInet]

Correct! There's something called ExecuteExcel4Macro function and this is a very old language that existed with the first versions of Excel. Using that doesn't open the file, but there's no reference document anywhere on the net for the language.

Even using ADO/DAO the file still needs to be loaded into memory and it takes relatively the same amount of time doing it via Excel automation.

 

[Richard1980]

[...]
Even using ADO/DAO the file still needs to be loaded into memory and it takes relatively the same amount of time doing it via Excel automation.

Roger, so don't you suggest ADO/DAO and go for the classic ".workbooks.open(...)"?

 

[vbaInet]

That's right. If you want to automate Excel, that's the way to go.