Load data from TXT to Excel sheet (numbers with decimal get like text)

[danovkos]

Hi
I have this code and i dont know, why, when importing data from txt file with this code to my excel sheet, it return numbers with decimal as text . Whole numbers are OK, but decimal numbers are as text.

I tried multiply variable with 1 also convert it with Abs(CDec(strTemp)) but i was not succesfull.

What i want is, that after importing data from txt file to sheet it will every number as number. Now only whole numbers are correct. So e.g. (number 1400 its OK, 345 its ok...), But number with decimal are not (1050,50 and 145,45 and.... there are as text)

I tried googled, but i was not succsessfull.
Pls. any suggestion how can i do it?
thx a lot



Dim a As Variant, b As Variant
Dim ws As Worksheet
Dim lngCounter As Long
Dim x As Long
Dim strTemp
Dim LastColumn As Long
Dim LastRow As Long
Dim Cesta As String
Dim Subor As String

Dim oFS As Object
Dim strFilename As String


Set ws = Workbooks(PrehladyNAMEVp).Worksheets("zrkadlo")

Workbooks(MyVP).Worksheets("mirror").Range("AZ4:BH10000").ClearContents
Range("A4").Select
Cesta = path1 & "path2"
Set oFS = CreateObject("Scripting.FileSystemObject")

Subor = "SudnePoplVRAT.txt"
Open Cesta & Subor For Input As #1

lngCounter = 1


ReDim b(1 To 8, 1 To 1)
Do While (EOF(1) = False)

lngCounter = lngCounter + 1
ReDim Preserve b(1 To 8, 1 To lngCounter)

Line Input #1, strTemp
a = Split(strTemp, vbTab)

For x = 0 To UBound(a)

If IsNumeric(a(x)) Then
a(x) = Abs(CDec(a(x))) * 1
End If

b(x + 1, lngCounter) = a(x)
' If IsNumeric(b) = True Then
' If IsNumeric(a(x)) = True Then
' a(x) = a(x) * 1
' End If

Next x
Loop
Close #1


ws.Cells(1, 52).Resize(UBound(b, 2), UBound(b, 1)).Value = WorksheetFunction.Transpose(b)

[p45cal]

Are you destination cells already formatted as Text?
Is Excel set to treat ',' (a comma) as a decimal point?

[danovkos]

Excel is set to comma as decimal...because i use it in other sheets as a decimal and it works.
But i formated whole column as number, because i import there numbers and it was still as text. But now i tried about 7000 cells in column multiplay with 1 and it now works. Also after new reuploading. So maybe its starts to work. Its strange. But thx. for try.

[Paul_Hossler]

Did want to convert to Decimal or Double?

Variables typed as Decimal:



+/-79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places. For numbers with 28 decimal places, the range is +/-7.9228162514264337593543950335. The smallest possible non-zero number is 0.0000000000000000000000000001.




Try this but format the WS cells as 'General' to remove another possible issue



a(x) = Abs(CDbl(a(x)))

[danovkos]

its start do it again. I formatted my Range many times to general or number, but after load data from txt file to sheet to my range, it start again. Numbers are only whole number as 500, 324 and.... but decimal, like 354,45 are not numbers. :(

And i tried also this:
a(x) = Abs(CDbl(a(x)))
but it is the same result.

i tried to format it again and now it works. Strange. So i hope, it will works. thx

[danovkos]

I am desperate.
It start change my numbers with decimal to text again. Again it appear as text not as numbers.
Any suggestions how to force, column in sheet as number?
It looks, that after restart of wb it change back to TEXT.
My bypass was, that i multiplay whole column with number and this works, but only till i restart my wb. :(

[GTO]

NOT Tested.

Instead of a(x) = Abs(CDbl(a(x))) have you tried a(x) = Val(a(x)) ? It is a guess, but Split returns strings, so unless Excel (the sheet) really super-understands that its a number that got plunked in, I thing it decides it must be a string.

[danovkos]

Yes, this works good, but, it is without decimal. :( I starting to think, it is because here in Europe, we use as decimal delimeter "," but excel want to use ".". I try to replace during loading txt file sigh "," with ".".

[GTO]

ACK! I tried Val and was not happy. It seems that the variant array wants to "hold onto" (for my lacking a better way of expressing it) the string sub-type. Anyways, I am sure there's a neater way, but until someone posts it, how about a second array?

By example only:



Sub example()
Dim a
Dim n As Long
a = Split("1.05,.002,3", ",")
ReDim b(0 To UBound(a)) As Double

Range("A1").Resize(, 3).Value = a

For n = 0 To 2
b(n) = CDbl(a(n))
Next

Range("A1").Resize(, 3).Value = b

End Sub

]

Mark

[Paul_Hossler]

Can you post a sample of the file?

[p45cal]

Can you post a sample of the file?I second that. Perhaps not only of the .txt file but also a workbook with one sheet and the code where you've been able to reproduce the problem.

[danovkos]

I solved it with this:


a(x) = Replace((a(x)), ",", ".")


BIG thank you to all