Search code examples
excelms-accessvbams-access-2007

Stop Access 2007 From Using Scientific Notation When Importing Data

So I am importing a giant list of part numbers from an Excel file (converted from a CSV report from somewhere else) with VBA. The excel workbook is called on to prepare the data before importing including the following code that formats the part numbers to make sure the part numbers don't lose any info (just recently caught this):

Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 2), TrailingMinusNumbers:=True

Once the excel sheets are made, I import them into tables using:

DoCmd.TransferSpreadsheet acImport, 5, "PartNumberX", "D:\Target\Import Data.xlsx", -1, "PartNumberX!" & PartDim

Somewhere between the excel file and the Access table, the part numbers that do not have letters in them and are longer than 10 digits are being converted to scientific notation and losing the last few digits on the number. I can confirm that the excel file does not have scientific notation in it so I'm fairly certain it's happening somewhere in the importing process.

Any help with this would be greatly appreciated!

-Edit:

When using DoCmd.TransferSpreadsheet it is creating the table "PartNumberX" - it does not already exist.

-Update:

So I thought that importing into a new table might be causing this issue. After importing into a pre-defined table with the part number field as a text field makes no difference in regards to preventing access form using scientific notation.

Solution

  • Looks like I had a dumb... I thought that since I formatted the original list of part numbers in Excel to be right the data a copied from it would also be formatted correctly. Obviously this was incorrect. I used 

    Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True

    to format the other iterations of the part number list and it works fine now.

    It's always the little things that get you -_-'