TransferSpreadsheet and datatypes 1

[SMB]

When I try and use the following code

DoCmd.TransferSpreadsheet acImport,_ acSpreadsheetTypeExcel97, "tbltmpImportFile",_ Me.txtInputFileLocation, True

One column in the spreadsheet contains mixed data types (numeric and text) - I have set the data type in the Access table to be text to cope with both but Access looks at the data type of the first few (?) rows in the spreadsheet and decides that they are numeric and then when it hits a text record generates a paste error!

I have tried linking to the spreadsheet but no go either.
Any help gratefully received!

 

[THoey]

Try to manually import the file. Press the advanced button and then select the Specs button. Change the type for that column from number to text. Save the spec again.

Hope that helps.


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[SMB]

I have tried manually. You dont get an advanced option and therefore no specs when importing SPREADSHEETS these are only there for text files.

 

[HokieFan]

I just ran into this same problem. Based upon what you said in your question, I changed the first cell of data in the field in question from numeric to text by putting a single quote at the beginning. Not pretty, but it worked.