Excel vba macro to convert long numbers correctly

[FindingInformation]

I have to convert a text file into excel 2010 for which I have written a marco

This file has column that contains long numbers and alpha numeric data.
My issue:
I have converted cell into Text(script below), alpha numeric number is fine however, when there are numbers more than 20 digits, excel is converting them with as 'E+15' or 'E+16' and changing last numbers to 00000.


Example: Excel Cell B should have -> but has this
8723894787834575644666 -> 8.72389478783457E+21(Incorrect)
AB8927349827 -> AB8927349827 (Correct)

This macro script converts column B to text however, it doesn't convert long numbers correctly. Any suggestions?

VB script I am using :
objExcel.columns(2).NumberFormat = "@"

Thanks in advance.

 

[pgc01]

This macro script converts column B to text however, it doesn't convert long numbers correctly. Any suggestions?

VB script I am using :
objExcel.columns(2).NumberFormat = "@"

Hi

No, this code does not convert the values in column B to text.

What this code does is to set the default value type for new values entered in column B to text.

Check the difference:

1 - You have the number value 123 in B2. After your code the value in B2 is still a number value

2 - You run your code and only then you enter 123 in the cell B2. In this case you'll have a text value "123" in B2

First run this code and only then paste the values.
If you still have problems post also the code where you import the values.

Remark:

Can you not import directly the text file into excel?

 

[FindingInformation]

Thanks for your reply.

I did exactly as you advised, converted the cell to text format and then copied the contents. Still I see formatting not being corrected.


Here is my script:

Sfile = "c:\users\admin\desktop\longnumber.txt"
target = "c:\users\admin\desktop\longnumbers.xlsx"




'Create Spreadsheet
'Look for an existing Excel instance.
On Error Resume Next ' Turn on the error handling flag
Set objExcel = GetObject(,"Excel.Application")


'If not found, create a new instance.


If Err.Number = 429 Then '> 0
Set objExcel = CreateObject("Excel.Application")
End If


objExcel.Visible = false
objExcel.displayalerts=false
objExcel.Application.SheetsInNewWorkbook = 2


'formatting column 2 as text


For intColumns = 2 To 2
objExcel.Columns(intColumns).AutoFit()
objExcel.Columns(intColumns).NumberFormat = "@"
Next








'Import CSV into Spreadsheet
Set objWorkbook = objExcel.Workbooks.Open(Sfile, , , 6, , , , , "|")
Set objWorksheet1 = objWorkbook.Worksheets(1)
Set xlWb = objExcel.ActiveWorkbook
'Get the sheet
Set xlWs = xlWb.ActiveSheet
xlWs.name="mysheet"
'Save Spreadsheet, 51 = Excel 2007-2010
objWorkbook.SaveAs target, 51
'clean up
'Release Lock on Spreadsheet
objExcel.Quit()
Set objWorksheet1 = Nothing
Set objWorkbook = Nothing
Set ObjExcel = Nothing



Output:

Test	8.97894E+24
Short S	9.32009E+12
test3	2.90309E+13
test4	jaj8237987jh
test5	ksdjf2908j

<colgroup><col><col></colgroup><tbody>
</tbody>

Is there a better way to do this?

Thanks again!

Hi

No, this code does not convert the values in column B to text.

What this code does is to set the default value type for new values entered in column B to text.

Check the difference:

1 - You have the number value 123 in B2. After your code the value in B2 is still a number value

2 - You run your code and only then you enter 123 in the cell B2. In this case you'll have a text value "123" in B2

First run this code and only then paste the values.
If you still have problems post also the code where you import the values.

Remark:

Can you not import directly the text file into excel?

 

[pgc01]

Hi

You did not post the input.

Looking at the output you posted, I'll assume your csv input file is something like:

Test1,12345678901234567890
Test2,abcd1234efgh5678ij90
...

If that's the case you can import directly the file to excel, specifying the types of the values in each column.
Do it first manually using the ribbon so that you can test that it works ok.

In excel 2013 the importing text files feature is in:

ribbon->Data->From text (this is in excel 2013. it may not be in the same place in other versions)

Set the second column to text in the import dialog.

After you see that it works ok, set the macro recorder on, and repeat the procedure to get an example of the code.

 

[FindingInformation]

Thanks for helping me out.

I did what you said and importing file works fine for long number column and here is the macro code.

Sub text()
'
' text Macro
'
' Keyboard Shortcut: Ctrl+t
'
Application.CutCopyMode = False
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\Admin\Desktop\longnumber.txt", Destination:=Range("$A$1"))
.CommandType = 0
.Name = "longnumber"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


Warm regards,

Hi

You did not post the input.

Looking at the output you posted, I'll assume your csv input file is something like:

Test1,12345678901234567890
Test2,abcd1234efgh5678ij90
...

If that's the case you can import directly the file to excel, specifying the types of the values in each column.
Do it first manually using the ribbon so that you can test that it works ok.

In excel 2013 the importing text files feature is in:

ribbon->Data->From text (this is in excel 2013. it may not be in the same place in other versions)

Set the second column to text in the import dialog.

After you see that it works ok, set the macro recorder on, and repeat the procedure to get an example of the code.

 

[pgc01]

I'm glad it helped.

Notice that in a more complex case you could also read the file and then manipulate the data yourself.

I did a quick text. Opened notepad and wrote some line with the format that I posted (each line 2 columns separated by a comma). Saved as ansi text.
This code imported the values to columns A:B of the active worksheet:

I you want to try it, set the reference to Microsoft Scripting

' import text file. 2 columns, import both as text
Sub AddFromTextFile()
Dim fs As FileSystemObject
Dim sPathname As String, sText As String
Dim vText As Variant
Dim j As Long

sPathname = "c:\tmp\longnumber1.txt"
Set fs = New FileSystemObject

sText = fs.OpenTextFile(sPathname).ReadAll
vText = Split(sText, vbNewLine)
For j = 0 To UBound(vText) ' Split each line using comma delimiter and write values in columns A:B
    Cells(j + 1, "A").Resize(, 2).Value = Split(vText(j), ",")
Next j
End Sub

Remarks:
1 - if I had thousands of lines, instead of writing 1 line at a time, I would store all the output in an array and only then would write it to the worksheet.
2 - if instead of a simple ansi text file I had a unicode text file, I'd use a text stream instead.