CPearsons import text code
 

Using the code below works well except that it drops the lead zeros
for a couple of the columns. Is their a way to retrieve exactly whats
in the text file.

Thanks

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' ImportTextFile
' This imports a text file into Excel.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' END ImportTextFile
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
End Sub

CPearsons import text code
 

hi
instead of Cells(RowNdx, ColNdx).Value = TempVal
use Cells(RowNdx, ColNdx).text = TempVal
might be helpfull ...not sure though
--
hemu


" wrote:

Using the code below works well except that it drops the lead zeros
for a couple of the columns. Is their a way to retrieve exactly whats
in the text file.

Thanks

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' ImportTextFile
' This imports a text file into Excel.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' END ImportTextFile
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
End Sub

CPearsons import text code
 

You could format the range that gets thes values as text.

with Cells(RowNdx, ColNdx)
.numberformat = "@"
.Value = TempVal
end with

But I'm not sure that I'd do that for all the fields--if you're importing dates
or times or money or ... (where you don't want any leading 0's), it might cause
problems down the road.

Have you though of leaving the cells formatted as General while you're doing the
import, then changing the number format for the fields you want to display
leading 0's?

After the import...
activesheet.range("a1,e1,j1").entirecolumn.numberf ormat = "00000"
(to get 3 columns to show 5 digits)

" wrote:

Using the code below works well except that it drops the lead zeros
for a couple of the columns. Is their a way to retrieve exactly whats
in the text file.

Thanks

'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' ImportTextFile
' This imports a text file into Excel.
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
' END ImportTextFile
'''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''
End Sub

--

Dave Peterson

CPearsons import text code
 

Try using .formula instead of .value

Cliff Edwards