Can someone please tell me where I am going wrong. I want to import a single worksheet from a workbook using Transferspreadsheet. This is what I have so far:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp", False, variableSheetName


I think there should be an exclamation mark at the end of this statement but if I put it in it doesn't like it.

Can you see what's wrong?

I think you have the parameters in the wrong order, but I am not sure.

try this:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp", variableSheetName, False

Here is the function parameter order:

TransferSpreadsheet(TransferType (acImport), SpreadsheetType(acSpreadsheetTypeExcel9), TableName ("tblTemp"), FileName (variableSheetName), HasFieldNames (False), Range (not specified so No), UseOA (not specified so no))

Hope that helps. You should not need a ! at the end.

GumbyD

I'm pretty sure the order of the parameters is correct and that it is the syntax that is wrong. Itried changing the order anyway but it still didn't work.

A case of making a silly mistake and then staring at it too long. Here is the answer if you're interested:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTemp", txtPath, False, shtName & "!"

(Forgot the exclamation)