Write a large number (16 digits) to a text file

[ddseager]

Column A contains account numbers, which are 16 digits long.
Column A is formatted to "0000000000000000".
When I Write this out to a text file, Excel tries to help by converting column A to Scientific Notation.

Here's the code:
While iCounter < iLastRow + 1
Set RngA = Range("A" & iCounter)
Set RngB = Range("B" & iCounter)
Set RngC = Range("C" & iCounter)
Write #2, RngA, RngB, RngC
iCounter = iCounter + 1
Wend

Is there a way to Write the contents of column A as 16 digits, not Scientific Notation?
Thanks

 

[GlennUK]

I have some qeustions:
are the account numbers keeping the 16 digits in the cells formatted as "0000000000000000" as I'd have thought that numerics stored that way would round at around 13 digits. I'd have used text formatted cells for storing such numbers.

How are you checking the text file after it's been created? Don't say that you open it in Excel, as that will show what Excel translates the contents as, and not the actual contents ... you should be using a text editor to check the output.

 

[ddseager]

GlennUK
The account numbers are stored as text. I even put a ' in the first postion to guarantee it. However, when I select the cell in order to Write it, Excel automatically changes it to Scientific Notation. I use NotePad to look at the output CSV file and every other field is formatted correctly, even all numeric fields. Although, all other numeric fields contain fewer than 12 digits.

I put a MsgBox statement in the macro. So I can see the value of RngA in the MsgBox and at the same time I can see the value in column A. Column A has 16 digits and the MsgBox has Scientific Notation.

While iCounter < iLastRow + 1
Set RngA = Range("A" & iCounter)
MsgBox RngA
Set RngB = Range("B" & iCounter)
Set RngC = Range("C" & iCounter)
Write #2, RngA, RngB, RngC
iCounter = iCounter + 1
Wend

Thanks

 

[ddseager]

All,
For those of you who need to do this, I figured out the answer. Before when I was addressing the Account Number (RngA) I was addressing the Value. The solution is to address the Text. When you addresst he Text, Excel does not convert the cell content to Scientific Notation. It leaves it as it is formatted.
Thanks,


Here's the code:
While iCounter < iLastRow + 1
Set RngA = Range("A" & iCounter)
vTempA = RngA.Text
Set RngB = Range("B" & iCounter)
Set RngC = Range("C" & iCounter)
Write #2, vTempA, RngB, RngC
iCounter = iCounter + 1
Wend