When using OLE DB (ODBC provider)  with EXCEL as the datasource, Is
there a default "table name" to the data block in the spread-sheet? Or
do I always
have to name a data block using Insert/Name/Define?

Hi,

If you do not want to provide the name for a data block using
Insert/Name/Define, then the only other option that you have is to use
the data in the entire worksheet.  The name of the worksheet (which
can be seen at the bottom, generally "Sheet1" or "Sheet2" or "Sheet3")
defaults to the "table name".  This table name will apply to the
entire matrix starting from Row1, ColumnA to the last row/column on
that worksheet that contain any data.

The article (link given below) clearly explains that the only two
methods of querying Excel tables via ODBC is:
1. define a range, or
2. use the worksheet name

Here's the link: [http://www.rsinc.com/services/output.cfm?tip_id=3319]

I knew about this topic before and hence, did not have to do any
searching at all, in fact the article above was something I had
bookmarked before.

I hope you like the answer.  Feel free to request for clarification,
if needed.

Regards,
gopalkamat-ga

---

Request for Answer Clarification by avner-ga on 18 Aug 2002 00:01 PDT

Thanks for your answer. I am not statisfied with it yet.
Two issues:
1. Your link is not working. I have tried also http://www.rsinc.com
and it is not working also...
2. I am writing an OLE DB cosumer and your solution is not working for
me. When trying to open the schema rowset through the ATL template
CTables without defining the ranges within the spreadsheet the schema
code does not provide me with the name of the spreadsheet the same way
that you describe.

Is there another way to retrieve the table name or names from the
provider through some generic OLE DB property?

---

Clarification of Answer by gopalkamat-ga on 18 Aug 2002 06:19 PDT

Hi avner-ga,

I apologize that the URL is not working now and is broken.  There's
another article I found that suggests the same ways of working with
Excel datasources, i.e. using worksheet name or using named ranges. 
You can find it here:
http://sites.encompassnow.com/idude/articles/using_excel_file_datasources.asp

As for point number 2 mentioned in your request for clarification,
what did you mean when you said "OLE DB cosumer"?? I am not aware what
the word 'cosumer' means.

As for the two ways that I've mentioned in my answer for querying
Excel datasources, both these have worked for me in the past using ASP
(Active Server Pages) and VB (Visual Basic) too.  The versions I have
used ASP 4.0 and VB 6.0 with Excel 2000 datasources.

I am afraid these are the only possible means of communicating with
Excel datasources.  This has also been mentioned by Microsoft in its
article on "How To Query and Update Excel Data using ADO via ASP"
here:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q195951&

I strongly believe that even though I have cited these two ways of
querying Excel data via ODBC, the only conventional and supported
method for this is the one that uses Named Ranges.  What I mean to say
that most of the websites that talk about Excel connectivity via ODBC
mention only the "Named Range" method and nothing else.

Maybe someday in the future, Microsoft will come up with more/better
provisions for data querying with Excel. :-)

I hope I have satisfied your curiosity this time.  Once again, let me
know if you have further questions regarding this.

Regards,
gopalkamat-ga

Very good reference was given on the second attempt. The reference
answered my question exactly.