Problem while fetching Excel records?

[Biju]

Hi,
Can you please tell me what is the difference between
an Excel sheet created using the Export functionality of
MSAccess and a manualy entered Excel Sheet?

When I'm fetching the records from Excel generated by
MSAccess, i'm able fetch record using ADO.

If i'm connecting to a manually enetered Sheet, it is
showing empty records. What is the reason behind this?

I'm strucked up with this.

regards
Marshal

 

[Dick Kusleika]

Marshal

You should be able to ADO any Excel sheet as long as it has a defined table.
Usually that means a named range. When you export from Access, there
doesn't seem to be any name range, but there must be some sort of implicit
table definition - that's just a guess.

If you create a named range in your manually entered spreadsheets, you
should have no problem ADO-ing them.

 

[onedaywhen]

A 'named range' is not a prerequisite for ADO to be able to define a
'table'. Rather, the data needs to be organized as rows of contiguous
columns, preferably with column headings. Using the sheet name in a
query, e.g.

SELECT * FROM [Sheet1$]

will pick up a single table regardless of its location on the sheet.

When there are more than one 'table' on a sheet you need to tell ADO
where to look. This can either be a defined name or the range
encompassing the top and bottom of the table (you can overshoot on the
rows) e.g.

SELECT * FROM [Sheet1$B20:E65536]

 

[Dick Kusleika]

Thanks for the clarification.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

A 'named range' is not a prerequisite for ADO to be able to define a
'table'. Rather, the data needs to be organized as rows of contiguous
columns, preferably with column headings. Using the sheet name in a
query, e.g.

SELECT * FROM [Sheet1$]

will pick up a single table regardless of its location on the sheet.

When there are more than one 'table' on a sheet you need to tell ADO
where to look. This can either be a defined name or the range
encompassing the top and bottom of the table (you can overshoot on the
rows) e.g.

SELECT * FROM [Sheet1$B20:E65536]


"Dick Kusleika" <[email protected]> wrote in message

Marshal

You should be able to ADO any Excel sheet as long as it has a defined table.
Usually that means a named range. When you export from Access, there
doesn't seem to be any name range, but there must be some sort of implicit
table definition - that's just a guess.

If you create a named range in your manually entered spreadsheets, you
should have no problem ADO-ing them.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.