Worksheet name as part of Data Source parameter

[weboweb]

Hi everybody,

When I connect to an Excel data source using Jet 4.0 I use something
like:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\typical.xls ...
extended properties bla bla bla

Is it possible when setting up the connection string to also indicate
the Worksheet Name?
Something like:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\typical.xls[Sheet1]
???

I guess not, but I would like to hear it from the pros. If at all
possible, would somebody please post the correct syntax for this? I
tried the above syntax and it didn't work of course.

What I'm trying to accomplish is that once the connection is made and I
retrieve the schema I would only see information regarding the Sheet1
worksheet and not the whole excel file.

In SQL this would be ridiculous of course, but Excel is so strange
anyway...

It's probably some crazy idea but who knows!

Thanks very much

webOweb

 

[Guest]

Hi everybody,

When I connect to an Excel data source using Jet 4.0 I use something
like:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\typical.xls ...
extended properties bla bla bla

Is it possible when setting up the connection string to also indicate
the Worksheet Name?
Something like:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\typical.xls[Sheet1]
???

I guess not, but I would like to hear it from the pros. If at all
possible, would somebody please post the correct syntax for this? I
tried the above syntax and it didn't work of course.

What I'm trying to accomplish is that once the connection is made and I
retrieve the schema I would only see information regarding the Sheet1
worksheet and not the whole excel file.

In SQL this would be ridiculous of course, but Excel is so strange
anyway...

It's probably some crazy idea but who knows!

Thanks very much

webOweb

Add "$" to the name of the Sheet (SheetName$)

 

[weboweb]

From Canary Islands wrote:

Hi everybody,

When I connect to an Excel data source using Jet 4.0 I use something
like:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\typical.xls ...
extended properties bla bla bla

Is it possible when setting up the connection string to also indicate
the Worksheet Name?
Something like:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\typical.xls[Sheet1]
???

I guess not, but I would like to hear it from the pros. If at all
possible, would somebody please post the correct syntax for this? I
tried the above syntax and it didn't work of course.

What I'm trying to accomplish is that once the connection is made and I
retrieve the schema I would only see information regarding the Sheet1
worksheet and not the whole excel file.

In SQL this would be ridiculous of course, but Excel is so strange
anyway...

It's probably some crazy idea but who knows!

Thanks very much

webOweb

Add "$" to the name of the Sheet (SheetName$)

Thanks for the response, but that does not work either (had already
tried that). That would work if I were doing a SELECT * FROM [Sheet1$]
for example, but not as part of the connection string.
What I'm doing now is getting the field count and examining each column
for data type, etc.
I was just wondering if I could apply the OpenSchema to a worksheet
instead of to the whole excel file.

Later!
webOweb