I appear to be having a peculiar problem with importing an Excel file. The spreadsheet contains 6 columns - the first three are in text format (though the data in the first two appear as numbers), the fourth column is date format (medium date - ie, 00-Jan-00), fifth is number format with zero decimals, and sixth is currency with zero decimals.

I have a table in Access with the same data structure, and the column headers in Excel match the field names in Access.

I have used the following code on an OnClick event. When I specify a new table in the argument (ie, one that doesn't exist in Access), it works fine. On the other hand, if I specify the table I want the Excel data to populate, it doesn't. I once managed to get data imported using the menu, and added some rows of data to the spreadsheet. What I would like it to accomplish is to append to existing data with the new data in Excel.


Private Sub cmdUploadSalesData_Click()
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "tblProductSales", _
"U:\ABCdatabase\Spreadsheets\Upload-Test\ABCUploadSalesData0404.xls", True
End Sub

Any suggestions? This is an urgent request and all help is greatly appreciated. Ideally, I would also like to have code in the above that forces the Excel data to the data structure in the Access table.