Excel as a datasource - what's the table name?

[ptj]

Hi I'm trying to use an excel spreadsheet (with the first row containing column names and the rest containing data) as a datasource.

I have built a connection string that works, but when I try to bring back a recordset based on some SQL I fail because I don't know what the 'table name' is for an Excel sheet.

I am connecting using OLEDB (for DSN) I think and the connection code goes through, but when I query using the sql: "SELECT * FROM table" it always gives an error of object not found. I have tried the sheet name, 'Sheet1', the file name, and a variety of other things to no avail.

Anyone got any bright ideas? I am doing some automated data conversion into access from the sheet, so getting a recordset would be a good start, but an alternative would be to get it into a temporary table where I can start to work on it!

Thanks in anticipation

Phil.

 

[petermeachem]

I need to get this working again myself. I think teh format is
SELECT [Sheet1$].[Title] AS Title , [Sheet1$].[Firstname] AS FirstName , [Sheet1$].[Surname] AS LastName , [Sheet1$].[address] AS Address From [Sheet1$]

My connection string is
Provider=Microsoft.Jet.OLEDB.4.0;Data Source= D:\Letters Direct\Letters Direct.xls;Extended Properties=Excel 5.0;

Seems to work a bit. Peter Meachem
peter@accuflight.com