View Single Post
 
Old July 10th, 2007, 11:28 AM
stealthdevil stealthdevil is offline
Friend of Wrox
 
Join Date: May 2006
Location: Jonesboro, AR, USA.
Posts: 144
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the reply Shasur!
I tried to use the above code, but never got it going quite right.
I did find a way to do import the text file though, using the code below.
Code:
Public Sub ImportTextFile()
    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
    Dim Sep As String
    Dim FName As String

    Cells.Select
    Range("A2:V3500").Select
    Selection.ClearContents

    FName = "Y:\INCOMING\sorter1.txt"
    Sep = ","

    Application.ScreenUpdating = False
    'On Error GoTo EndMacro:

    SaveColNdx = 1
    RowNdx = 2

    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 Sub
The only problem I'm having now is that some of the fields have quotes. I need to get rid of the quotes. How can I accomplish this?

Thanks,
Dave