Solved: Text File import - recognising values

[Glaswegian]

Hi all

I'm using Chip Pearson's code to import a large text file and it mostly works well.

The one problem I have is one particular field that seems to confuse Excel. The text file is system produced and cannot be changed. The field in question looks like this example in the text file:

0001.750000

This should translate as 1.75 - and there will be various other values both higher and lower. In stepping through the code, I've found that Excel seems to see this as

" 0000000001"

with the code then deciding it should be a time format and changing it to

"00:00:00"

The section of code in question is this (around line 480 in Chip's routine)

If Colndx + C_START_COLUMN <= WS.Columns.Count Then
Dim tmp
tmp = Arr(Colndx)
If IsDate(tmp) Then tmp = DateValue(tmp)
WS.Cells(RowNdx, Colndx + C_START_COLUMN).Value = tmp 'Arr(Colndx)
Else
TruncatedCount = TruncatedCount + 1
Exit For
End If
Next Colndx
End If ' SplitChar = vbNullString

I believe it's the IsDate line that's causing the problem and I have been trying to trap the incorrect value but I'm not having any success.

Can anyone suggest a way around this problem?

[mdmackillop]

Can you test for that number format?

Sub Test()
Dim v
v = "0001.750000"
If v Like ("####.######") Then
v = CDbl(v)
End If
MsgBox v
End Sub

[Glaswegian]

Hi Malcolm

Thanks for your reply.

Sorry I could not reply earlier but I had to wait until I returned to work. Had to add an extra space at the end of the string but works great now - many thanks.