SQL query on Excel file with ADO/VB

I tried that already...but it doens't run.
I believe HDR=YES is default.

Try changing the last line of your connection string to:

"Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;"""

IMEX=1 will help ADO to not use datatypes that are inconsistent with the values in your spreadsheet and HDR=1 tells ADO that your column names are in the first row of the range that you specified...  (I am assuming that this is true.)  If your column names are not in [sheet1$A4:BE4] then you should make sure that they are or that you correct the range to include the column names.

Are you Dutch?  Or, rather, can you read Dutch?  If so then maybe you could give us a rough translation or the error message that you are recieving...  At least see if you can give us the error number.

Hi,
I tried adjusting the connectionstring but with the same result.

The column names are included in the range [sheet1$A4:BE4]

I know dutch.
This is a rough translation of the error message:

Runtime Error '-2147217904 (80040e10)':
Values for one or more required parameters missing

this works:
Set RST = Con.Execute("SELECT nr+nr as totaal FROM [projecten$A4:BE60] WHERE NR <> ''")
this doesn't:
Set RST = Con.Execute("SELECT [1]+[2] as totaal FROM [projecten$A4:BE60] WHERE NR <> ''")
I think something is wring with the "[" and "]" and the numbers that are used as columnnames. Oh..by the way...I can't change the column names in the excel sheet.

any ideas?

Can you change the column headers so they are not numeric?

Leon

Try using HDR=NO and excluding the column names from the range.  (i.e.  Use [projecten$A5:BE60].  Starting at row 5.)  Then you can use the default column names (F1, F2, F3.....).  Your query would then look like:  

"SELECT F1, F2 + F3 + F4 + F5 AS Total FROM [projecten $A5:BE60]"

1.  Do you really have 57 columns???  If so then do you need to include them all in your query or can you include only the columns that you need?  (i.e.  projecten$A5:E60)

2.  Is all of the data in the table numeric?  (You can modify the code below to print out the datatype of the columns if you are unsure.)

3.  Try using your code above (including the column header range) to SELECT * from your table and then loop through the columns and print the field names out and make sure that you have the names correct:

for i =0 to rst.fields.count - 1
    debug.pring rst.fields(0).name
next i

This does seem to work.
Is the sequence of F1, F2 etc always the same sequence as the corresponding column in the excelsheet?
F1=A
F2=B
F3=C
etc
are there any circumstances where this is not the case?

Thanks.... this is very helpfull.

I just checked...with HDR=YES, the columns with numbers as name are still named F5, F6, Fx.
Columns that have text as name have that text as their column name.

How d#mb of me not the look at the fieldnames in the first place.

Anyway...here are your points! Thanks again.

I tried running this, but it failed.  I think it's best to assume ignore the Excel part when looking at a csv or txt file, but am not sure how to code the connection string.  The worksheet name is test

Set con = CreateObject("ADODB.Connection")
StrCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=c:\test.csv;" & _
           "Extended Properties=Excel 8.0;IMEX=1;HDR=YES;"
con.Connectionstring = StrCon
con.open
Set rst = con.execute("SELECT * FROM test")