keepITcool wrote ..

be careful with the syntax... SELECT INTO statement has several
"syntaxis" (see KB 295646), but this is the only way I got it to work
with both CSV and XLS in a "one-liner"

I prefer the alternative syntax e.g.

SELECT MyCol FROM [Excel
8.0;HDR=YES;Database=C:\Tempo\db.xls].MyTable

The sequence <<database.<<table is more logical, it avoids those
empty quotes and it better on the eye IMO <g.

note the the source file name cannot contain spaces!

This is incorrect (perhaps you were referring to a specific syntax
rather than generally?). You merely surround the file name (table
name) with e.g. brackets:

SELECT MyCol FROM [Text;Database=C:\Tempo\;].[Gappy Gap#csv];

I think your code is connecting to the wrong object i.e. the ADO
connection should be to Excel. As you said, because you connected to
the source and the source is Text you had syntax problems (a
limitation or bug in the provider?) You can open a connection to a
non-existent workbook; remember, the provider creates the workbook,
worksheet and Excel table on the fly if required. Also, with the
provider you are using (OLE DB for Jet 4.0), FMT=Delimited in the
connection string has no effect; you must use a scheme.ini file to
specify format, however comma delimited is default. Thus:

.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & xlsFile & ";" & _
"Extended Properties='Excel 8.0;HDR=Yes'"

.Execute _
"SELECT * " & _
" INTO Import " & _
" FROM [Text;HDR=Yes;Database=" & csvPath & ";].[" & csvFile &
"];"

Jamie.

--