Tricky field formats in excel imports

[tonygg]

Hi. I have a spreadsheet that I want to import into access. The spreadsheet is auto generated from a system.



I am using DoCmd.TransferSpreadsheet acImport, , "SPREADSHEET", Filepath, True.

This imports the spreadsheet but on 2 of the fields it is losing precision. Basically it is ignoring the 2 digits after the decimal place. When looking at the spreadsheet I can see that in these 2 columns there is data physically there with 2 dps. However the excel format for the field is 'number with 0 dps". When the import runs in access it just imports the number rounded to nearest whole number. I guess it is using the excel field formats to decide how to import each field. I know I could go into the spreadsheet and change the formats pre import but the person using the system is not confident in excel and may mess something else up.

Is it possible to tell access to read the spreadsheet and retain the underlying data or tell it what formats to use per column/field. Basically override the excel formatting?

Many thanks

Tony

[ranman256]

Instead on TRANSFERSPREADSHEET method, attach the sheet as a linked table, then Append to an existing internal table.
this table will have predefined fields. With the decimal places. (Double)

sometimes, access guesses at what the excel sheet column format is. And fails.

this new way, you overwrite the old XL file with the new one, then run the append.

[tonygg]

Thank you. I will have a look at how to do this. Thanks

[tonygg]

Hi ranman256. I tried the new method. It allowed me to specify the fields in the table as ccy but still did not correctly import the values. It to the rounded number and inserted it rather than keeping the 2 dp. Is there another way?
Many thanks Tony