Transferspreadsheet wasn't a problem last time?

[PaulSpell]

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?

 

[GumbyD]

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

 

[PaulSpell]

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.

 

[PaulSpell]

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)