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 XML Converter Free Download::
It generates documents that contain the OLE DB or ODBC data merged into the original XML. documents with the same data as your original ODBC data source,
http://www.softpile.com/Development/Miscellaneous/Review_21685_index.htmlHOME |
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?
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 Special Edition Using Microsoft® Visual Studio for Enterprise ::
as a query engine, that exposes OLE DB interfaces but doesnt own the data that to not require a user to have previously set up an ODBC data source.
http://www.glib.hcmuns.edu.vn/ebook/computer/web/0-7897-1260-1/ch23/ch23.htmHOME |
ADO.NET 3.5 Cookbook | OReilly Media::
data provider, the OLE DB .NET data provider, or the ODBC .NET You want to connect to a Microsoft Excel workbook. Use the OLE DB .NET data provider.
http://oreilly.com/catalog/9780596101404/toc.htmlHOME |
 Red Hat's Rough Recovery From CFO Exit
 Windows Live Finds a New, Pre-installed Home
|
OLE DB/ODBC with EXCEL as the data source