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.numberformat = "00000"
(to get 3 columns to show 5 digits)
"(E-Mail Removed)" 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
|
Similar Threads
