Problem while fetching Excel records?

  • Thread starter Thread starter Biju
  • Start date Start date
B

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
 
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.
 
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]
 
Thanks for the clarification.

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

onedaywhen said:
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.
Click to expand...
Click to expand...
 
Back
Top