Thanks

It runs well but lists worksheets and named ranges

How can I identify the named ranges only and more important, how can
I copy the named range content to the clipboard?

Avi

If you explore the samples in the download link I gave you, you'll see
how to load the contents of a named range into a recordset. Once there
you can do whatever you want with the data. Assuming you want to put it
into the Clipboard for pasting somewhere, you can bypass that step and
transfer the data directly to wherever...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks I'll explore it

Strange behavior with the provided sample sales.xls (or any other wbk)
szSQL = "SELECT * FROM [Sales$A1:E19];"
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
Sheet1.Range("A1").CopyFromRecordset rsData

If a column contains both numerical and string values, only the numerical values are copied !!!

Any idea?

Looks as I found the solution for my own question

I have to use HDR=YES;IMEX=1"; in the connection string

Avi

Looks as I found the solution for my own question

I have to use HDR=YES;IMEX=1"; in the connection string

Avi

Ok! Glad you were able to figure it out. It does take a bit of practice
to catch on to the nuances related to working with text files and
workbooks, being they both have/use different criteria args in their
respective SQL statements. For this reason I use a wrapper function to
return the data, which accepts 'sSource$' args "mdb", "wkb" or "txt"
which uses a 'Select Case' construct to build the appropriate SQL
statement. Also required as arg is 'sSelect$' which contains the table
info for building the "SELECT FROM" part. I use it like this...

<snip
Set RS = GetRS(sTable, "mdb") 'database file
Set RS = GetRS(sTable, "wkb") 'Excel file
Set RS = GetRS(sTable, "txt") 'text file
</snip

The function also determines whether to use "Jet" or "Ace" depending on
Excel version (though Jet is still working in all). This allows me to
reuse the function for any number of recordsets I want to access during
runtime without having to 'hard code' every time. Once you're familiar
with how ADODB works you may want to build your own reusable function!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks again

Now I am facing a new annoying issue: numbers are copied as "number stored as text" format

Is there a straightforward solution?

BTW, where can I find the wrapper function and how is it used in VBA?

Avi

Thanks again

Now I am facing a new annoying issue: numbers are copied as "number
stored as text" format

Is there a straightforward solution?

You'll need to format the fields (columns) containing numbers/dates as
desired per your data layout on the target sheet.

BTW, where can I find the wrapper function and how is it used in VBA?

This is a 'make-your-own' thingy that you configure for how you (want
to) work with data. I don't have an encapsulated wrapper because I
rarely use ADODB, and when I do I use SQLite.

The same principles apply, though, and so you should be able to do same
for your VBA projects by putting everything ADODB related in a standard
module that you can import into any project as needed. My earlier
example of how to use such a function was a rather simplified sample of
a more complex process which is generic so I can use it with VB6
projects as well as VBA projects. This is stored in a text file from
which I grab snippets on an as needed basis. (I primarily use '.dat'
files for storing data so I can work with the standard VB[A] built-in
I/O functions and arrays)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

One more issue,please

In the sample you provide, I use
' Query based on a sheet-level range name.
'szSQL = "SELECT * FROM [EZZ$ddd];"

where EZZ is the sheet name and ddd the sheet-level range name

I get an error stating that the engine can't find the object EZZ$ddd

No chance also with a workbook level name

Thanks again for your very valuable help