Runtime error 3265

[Dave_D]

I get a runtime error 3265 - Item cannot be found in the collection corresponding to the requested name or ordinal of the following statement:



Docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "TRD", rst.fields("Directory_path"), True, "F:F,K:K,O:O"

where my intentions are to import only those 3 columns.

[Micron]

You are doing this in Access or Excel? I believe you cannot specify a range for exporting from Access to Excel but you can if importing in Excel. Still, I wouldn't expect that error. Perhaps more importantly, I also believe you cannot use a non contiguous range regardless.

[Dave_D]

This is being done in access

[CJ_London]

for split ranges (assuming they are in the same sheet 'table') use a sql statement instead.

your basic code would be something like

strSQL="INSERT INTO TRD (Field1, Field2, Field3)" & _
" SELECT * FROM (SELECT Field1, Field2, Field3 FROM [sheet1$] AS xlData IN '" & rst.fields("Directory_path") & "'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes])  AS XL"
currentdb.execute strSQL

Change field and sheet names to suit.

[Micron]

This is being done in access

That leaves me with the reason being that you cannot specify non contiguous ranges. Ajax's solution would be better (as long as it works for you of course) - and simpler than automation.