Access - read from text file

[guerillaaccess]

Hi, I have a text file that I am using VBA to read and print 2 pieces of data to another text file.

The code works to a point, in some instances it does not appear to find the end of file but captures and writes all the data. Or, after printing 36 lines correctly, it then stops writing.

I have stepped through the code and it appears to be capturing the data up past this issue but for what ever reason it does not print it to my text file. :banghead:

Anyone able to have a look and maybe offer some guideance as to what I'm doing wrong?

Cheers.

Option Compare Database
Option Explicit
    Public Mode As String
    Public FUNDREF As String
    Public rst As String
 
Function DAILYAUDITTRAIL()
 
    Close #1
    Close #2
 
 
    Open "C:\Users\TEST IN.TXT" For Input As #1
    Open "C:\Users\TEST OUT.txt" For Output As #2
 
 
    Do While Not EOF(1)
 
    Line Input #1, rst
 
    If rst Like "KM56Aasdf*" Then
        FUNDFINDER
    End If
 
    Loop
 
 
End Function
Function FUNDFINDER()
 
 
        Do While Not rst Like "*RUN TERM*"
 
            Do Until rst Like "FM: Mode:*"
 
                If rst Like "*RUN TERM*" Then
                GoTo Printer
                End If
 
              Line Input #1, rst
 
             Loop
                Mode = Trim(Right(rst, 6))
                    Do Until rst Like "Fund Reference *"
                        Line Input #1, rst
                    Loop
                        FUNDREF = Right(rst, 6)
            Print #2, Mode & ";" & FUNDREF
 
           Loop
 
 
Printer:
End Function

 

[James Dudden]

Just tried running through this and it produced an output text file with 240 lines with the following repeated:

Amend;111111
Amend;222222
Amend;333333
Amend;444444
Amend;555555
..etc

I am using Access 2010 on Windows 7.

 

[guerillaaccess]

Just tried running through this and it produced an output text file with 240 lines with the following repeated:

Amend;111111
Amend;222222
Amend;333333
Amend;444444
Amend;555555
..etc

I am using Access 2010 on Windows 7.

Thanks James, I'm using the same version of Access and Windows as you.

Its throwing a "Run-Time error '62': Input past end of file" for me, did you get the same?

 

[James Dudden]

Yes, I did but I think this is because it has reached the end. Try the amended code below. I have added a line count to help check what's going on and extra break points in your loop for when the filehas reached the end.

Option Compare Database
Option Explicit
    Public Mode As String
    Public FUNDREF As String
    Public rst As String
    Public LineNum As Long
 
Function DAILYAUDITTRAIL()
 
    Close #1
    Close #2
    LineNum = 0
 
    Open "C:\Users\TEST IN.TXT" For Input As #1
    Open "C:\Users\TEST OUT.txt" For Output As #2
 
 
    Do While Not EOF(1)
 
    Line Input #1, rst
    LineNum = LineNum + 1
    If rst Like "KM56Aasdf*" Then
        FUNDFINDER
    End If
 
    Loop
 
    MsgBox "Finished"
End Function
Function FUNDFINDER()
 
 
        Do While Not rst Like "*RUN TERM*" And Not EOF(1)
            Do Until rst Like "FM: Mode:*" Or EOF(1)
 
                If rst Like "*RUN TERM*" Then
                GoTo Printer
                End If
 
              Line Input #1, rst
                LineNum = LineNum + 1
             Loop
                Mode = Trim(Right(rst, 6))
                    Do Until rst Like "Fund Reference *" Or EOF(1)
                        Line Input #1, rst
                        LineNum = LineNum + 1
                    Loop
                        FUNDREF = Right(rst, 6)
            Print #2, Mode & ";" & FUNDREF
 
           Loop
 
 
Printer:
End Function