DoCmd TransferSpreadsheet Range Worksheet Define 1

[JoeVegas]

How do I correctly define the range in the transfer spreadsheet function. Here is what I have defined:

Sub TransExcel2Access()
'Import specified Excel worksheet / range of data

DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadSheetType:=SpreadsheetTypeExcel9, _
Tablename:="TestImport2Access", fileName:="D:\Import\06022009\TestData.xls", _
Hasfieldnames:=False, Range:="Worksheet(1)(Data$)!A3161"

End Sub

The worksheet I need to import to Access is named "Data". I get the message stating - the Jet engine cannot find the object Worksheet(1)(Data$)$A3:161 - can someone help TIA

 

[PWise]

how about

"Data!A3161"

 

[JoeVegas]

That did it, Thanks !!

 

[JoeVegas]

Just had a thought, why not import the data from the TransferSpreadSheet Excel data directly into the related Access table.

 

[Countymnca]

I had tried the direct import to table approach and had numerous problems. Since going back to the TransferTBL and then apphending to the main table method, everything has worked great for the last 3+ years. I dont recall the problems specificly, but I have none since the change back.

 

[JoeVegas]

I found as long as you include the matching field names and data types for all fields it works flawlessly.