Save recordset til tabel

DoCmd.RunSQL "INSERT INTO YourTable (FieldList) VALUES (ValueList)"

FieldList = each field you are inserting data into
ValueList = the actual values

If you want to remove the warning you get then use

before you start looping the recordset
Docmd.Setwarnings False

after you are finished looping...
Docmd.Setwarnings True

I made it work using 'AddNew', but is it REALLY anoying that I have to specify each field I want to add (e.g. rsTable!RawMaterialName = rsExcel.Fields(0), rs.Table!RawMaterialType = rsExcel.Fields(1)... etc.) Isn't it possible to make some kind loop to handle this?

Something like...

    strCON = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Excel\ImportTemplate.xls;" & _
            "Extended Properties=Excel 8.0;"                 
    strSQL = "SELECT * FROM [Sheet1$A1:AN" & AnalysesNO + 1 & "]"
   
rsExcel.Open strSQL, strCON, adOpenForwardOnly, adLockReadOnly, adCmdText

strSQL = "SELECT * FROM [Chemical Analysis]"
Set rsTable = CurrentDb.OpenRecordset(strSQL)

While Not rsExcel.EOF
    rsTable.AddNew
    For j = 0 To rsExcel.Fields.Count - 1
      rsTable!Fields(j) = rsExcel.Fields(j)
    Next j
    rsTable.Update
    rsExcel.MoveNext
    Wend

or maybe something like this...

    For j = 0 To rsExcel.Fields.Count - 1
        rsTable! "& rsTable.Fields(j).Name &" = rsExcel.Fields(j)
    Next j

I understand that you wnat the import routine to be as flexible as possible, but as far as I see it you have a destination table which you know the layout of so you must obviously need to know that layout of the data you are importing. So I cant see wht you cant use the soultion i gave here 11/12-2003 16:54:29
or am I missing something?

I'm pretty sure that your solution works just fine, I was just looking for a way to avoid having to write every single 'Field' and corresponding 'Value' illustrated below. Currently, each row in Excel has 41 Values that have to be exported into the table and I was hopind this could be done using some kind of loop...

DoCmd.RunSQL "INSERT INTO "Chemical Analysis" (RawMaterialName, TestNO, Facility, Date, SiO2, Al2O3...etc) VALUES (rsExcel.Fields(0), rsExcel.Fields(1), rsExcel.Fields(2), rsExcel.Fields(3), rsExcel.Fields(4)... rsExcel.Fields(40))"

Btw, the data is imported from an excel template that I constructed, which means that I know the exact layout of both data origin and destination. Is that sufficient to make an import loop, or is it simply not possible to make such a loop...

Anywho, thx for your help and merry X-mas

Yes it is possible, but the work involved doesnt make it worth while!
The loop would have also need to find the data type of each field as the format of the values list is different for numbers and text.

and a Merry X-Mas to you too :o)