Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Why not seperate lines? (Access 97 VBA)

    I am attempting to import this attached file using the following code:
    <pre>Sub ImportTest()
    'Open the file to import
    Dim intFileNum As Integer
    intFileNum = FreeFile

    Open "C:ebill_R_Audit_11_20021210.txt" For Input As #intFileNum

    Dim strFromFile As String

    While Not EOF(intFileNum)
    Line Input #intFileNum, strFromFile
    Debug.Print "zzz"
    Debug.Print strFromFile
    MsgBox strFromFile
    Wend
    Close #intFileNum
    End Sub</pre>



    Why do I get only 1 msgbox with the full contents of the file and not 3 with 1 line each?
    Attached Files Attached Files
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why not seperate lines? (Access 97 VBA)

    The problem is the text file.

    The lines are not separated by carrige returns and line feeds (chr(13)+chr(10)). They are only separated by line feeds.(chr(10))

    From the help on Line Input#:

    <blockquote><hr>The Line Input # statement reads from a file one character at a time until it encounters a carriage return (Chr(13)) or carriage return
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Why not seperate lines? (Access 97 VBA)

    See my <!post=Post #138639,138639>Post #138639<!/post> for some suggestions on using the FileSystemObject methods to parse text files. Of course, there is much more here in the Lounge on that...

    Afterthought: Because you are using Access 97, which is VBA 5.x, you do not natively have the Split() function, which is the perfect tool for turning a blob of delimited text into a well-ordered array. But I wonder if it's possible to tap the Split() function through the Scripting runtime? That would be sweet. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why not seperate lines? (Access 97 VBA)

    I opened the data file in Word 2k and saved it back. Word replaced the linefeeds with cr/lf and the routine processed the file just fine.
    Legare Coleman

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Why not seperate lines? (Access 97 VBA)

    That's a great idea. I routinely use WordPad to open .txt files that Notepad cannot parse due to nonstandard line terminators. Word is better for the poster because it can be automated.

  6. #6
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why not seperate lines? (Access 97 VBA)

    I ended up writing a work-around:
    <pre>Sub ImportTest()
    'Open the file to import
    Dim intFileNum As Integer
    intFileNum = FreeFile


    Open "Myfile.txt" For Input As #intFileNum

    Dim strFromFile As String

    While Not EOF(intFileNum)
    Line Input #intFileNum, strFromFile
    Dim intLength
    For intLength = 1 To Len(strFromFile)
    Dim strIndividualRecord As String
    If Asc(Mid(strFromFile, intLength, 1)) = 10 Or _
    Asc(Mid(strFromFile, intLength, 1)) = 13 Then
    'End of the record
    If Len(strIndividualRecord) > 0 Then
    Debug.Print "-----------"
    Debug.Print strIndividualRecord
    End If

    strIndividualRecord = ""
    Else
    strIndividualRecord = strIndividualRecord + Mid(strFromFile, intLength, 1)
    End If
    Next intLength
    Wend
    Close #intFileNum
    MsgBox "Done!"
    End Sub</pre>


    Thanks for all the suggestions!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •