Stop scientific notation on import

[Guest]

I am running a simple import from my macro. One of the fields in my excel
spreadsheet has values that are sometimes numeric and aphanumeric. The
destination field in the database is formatted as text so this generally
imports successfully.

The import code...

DoCmd.TransferSpreadsheet acImport, 8, "tbl_Japan_Sales", impdir & strFile,
True, "A:J"


However, when I import longer values such as 307030012 the figure loaded
into the text field is 3.0703e+008.

How can I stop this.

Bruce

 

[John Nurick]

Hi Bruce,

One way is to prefix the values in Excel with an apostrophe. This forces
Excel and Access to treat them as text, although they are not displayed
in Excel or imported to Access. The Excel VBA function below will add an
apostrophe to the cells in the selection:

Sub AddApostrophesAllToSelection()
Dim C As Excel.Range
With ActiveWorkbook.ActiveSheet
For Each C In Intersect(Selection,
..UsedRange).SpecialCells(xlCellTypeConstants).Cells
C.Formula = "'" & C.Formula
Next
End With
End Sub