Find/retrieve text from a .txt file through Access/VBA

Discussion in 'Microsoft Access VBA Modules' started by AccessARS, Feb 8, 2008.

  1. AccessARS

    AccessARS Guest

    We have a proprietary system that generates daily processing logs in text
    files into a specific folder. These log files have a systematic naming
    convention i.e “Test_20080206 LOG.TXTâ€, “Test_20080207 LOG.TXT†depending on
    the date. I am building a system in Access/VBA that will search for a line
    with a specific error message within the most current log file thereafter
    store that line item into a table for reporting purposes in Access.

    Example of error line in log text file:
    “09:51:37 I Processing stopped by user.â€

    I can identify the most recent file and point to the location on a daily
    basis in vba but can’t seem to find any solid information on opening,
    searching and pulling text from the said text file. I’ve heard that there
    are methods for going through a text file line by line but have no clue where
    to start. If someone can give me a little direction I should be able to pull
    things together from there.
    Thank you in advance for your assistance.
     
    AccessARS, Feb 8, 2008
    #1
    1. Advertisements

  2. Does the file have "many" lines of text, or just one line of text??

    You can open a text file as follows:

    Sub ReadTextFile

    Dim strFile As String
    Dim intF As Integer
    Dim strLineBuf As String
    Dim lngLines As Long
    Dim lngBlank As Long

    strFile = "c:\my data\MyData.txt"

    intF = FreeFile()
    Open strFile For Input As #intF

    Do While EOF(intF) = False
    Line Input #intF, strLineBuf
    If Trim(strLineBuf) = "" Then
    lngBlank = lngBlank + 1
    Else
    lngLines = lngLines + 1
    End If
    Loop
    Close intF

    End If

    MsgBox "Number non blank lines = " & lngLines & vbCrLf & _
    "Blank lines = " & lngBlank & vbCrLf & _
    "Total = " & lngBlank + lngLines

    End Function

    You can also use the split() command to parse out the one line if it is
    delimonted...

    And, you could simply add to the above loop code add the one record to a
    file


    dim rstData as dao.RecordSet

    set rstData = currentdb.OpenRecordSet("tblMyData")
    rstData.AddNew
    rstData!FristName = "some text"
    rstData!LastName = "some more text"
    rstData.Update
    rstData.Close


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
     
    Albert D. Kallal, Feb 8, 2008
    #2
    1. Advertisements

  3. AccessARS

    Ralph Guest

    You could also use FileSystemObject

    sub readTxtFile
    dim fso
    dim f
    dim ts
    dim strLine as string

    set fso=CreateObject("Scripting.FileSystemObject")
    set f=fso.getfile("c:\temp\Test_20080206 LOG.TXT")
    set ts=f.OpenAsTextStream

    'loop through each line in text file
    do while not ts.AtEndfOfLine
    strLine=ts.readline
    'test for the line your looking for here
    debug.print strline
    loop

    ts.close
    set fso=nothing
    set f=nothing
    set ts=nothing
    end sub

    "AccessARS" wrote:

    > We have a proprietary system that generates daily processing logs in text
    > files into a specific folder. These log files have a systematic naming
    > convention i.e “Test_20080206 LOG.TXTâ€, “Test_20080207 LOG.TXT†depending on
    > the date. I am building a system in Access/VBA that will search for a line
    > with a specific error message within the most current log file thereafter
    > store that line item into a table for reporting purposes in Access.
    >
    > Example of error line in log text file:
    > “09:51:37 I Processing stopped by user.â€
    >
    > I can identify the most recent file and point to the location on a daily
    > basis in vba but can’t seem to find any solid information on opening,
    > searching and pulling text from the said text file. I’ve heard that there
    > are methods for going through a text file line by line but have no clue where
    > to start. If someone can give me a little direction I should be able to pull
    > things together from there.
    > Thank you in advance for your assistance.
    >
     
    Ralph, Feb 8, 2008
    #3
  4. You could, but why introduce the overhead of FSO when it's not required?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Ralph" <> wrote in message
    news:...
    > You could also use FileSystemObject
    >
    > sub readTxtFile
    > dim fso
    > dim f
    > dim ts
    > dim strLine as string
    >
    > set fso=CreateObject("Scripting.FileSystemObject")
    > set f=fso.getfile("c:\temp\Test_20080206 LOG.TXT")
    > set ts=f.OpenAsTextStream
    >
    > 'loop through each line in text file
    > do while not ts.AtEndfOfLine
    > strLine=ts.readline
    > 'test for the line your looking for here
    > debug.print strline
    > loop
    >
    > ts.close
    > set fso=nothing
    > set f=nothing
    > set ts=nothing
    > end sub
    >
    > "AccessARS" wrote:
    >
    >> We have a proprietary system that generates daily processing logs in text
    >> files into a specific folder. These log files have a systematic naming
    >> convention i.e "Test_20080206 LOG.TXT", "Test_20080207 LOG.TXT" depending
    >> on
    >> the date. I am building a system in Access/VBA that will search for a
    >> line
    >> with a specific error message within the most current log file thereafter
    >> store that line item into a table for reporting purposes in Access.
    >>
    >> Example of error line in log text file:
    >> "09:51:37 I Processing stopped by user."
    >>
    >> I can identify the most recent file and point to the location on a daily
    >> basis in vba but can't seem to find any solid information on opening,
    >> searching and pulling text from the said text file. I've heard that
    >> there
    >> are methods for going through a text file line by line but have no clue
    >> where
    >> to start. If someone can give me a little direction I should be able to
    >> pull
    >> things together from there.
    >> Thank you in advance for your assistance.
    >>
     
    Douglas J. Steele, Feb 8, 2008
    #4
  5. AccessARS

    Ralph Guest

    Yes I've seen the following, however I still like the flexibility. To each
    his own.

    http://vbnet.mvps.org/index.html?code/fileapi/fsoapicompare.htm

    "Douglas J. Steele" wrote:

    > You could, but why introduce the overhead of FSO when it's not required?
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Ralph" <> wrote in message
    > news:...
    > > You could also use FileSystemObject
    > >
    > > sub readTxtFile
    > > dim fso
    > > dim f
    > > dim ts
    > > dim strLine as string
    > >
    > > set fso=CreateObject("Scripting.FileSystemObject")
    > > set f=fso.getfile("c:\temp\Test_20080206 LOG.TXT")
    > > set ts=f.OpenAsTextStream
    > >
    > > 'loop through each line in text file
    > > do while not ts.AtEndfOfLine
    > > strLine=ts.readline
    > > 'test for the line your looking for here
    > > debug.print strline
    > > loop
    > >
    > > ts.close
    > > set fso=nothing
    > > set f=nothing
    > > set ts=nothing
    > > end sub
    > >
    > > "AccessARS" wrote:
    > >
    > >> We have a proprietary system that generates daily processing logs in text
    > >> files into a specific folder. These log files have a systematic naming
    > >> convention i.e "Test_20080206 LOG.TXT", "Test_20080207 LOG.TXT" depending
    > >> on
    > >> the date. I am building a system in Access/VBA that will search for a
    > >> line
    > >> with a specific error message within the most current log file thereafter
    > >> store that line item into a table for reporting purposes in Access.
    > >>
    > >> Example of error line in log text file:
    > >> "09:51:37 I Processing stopped by user."
    > >>
    > >> I can identify the most recent file and point to the location on a daily
    > >> basis in vba but can't seem to find any solid information on opening,
    > >> searching and pulling text from the said text file. I've heard that
    > >> there
    > >> are methods for going through a text file line by line but have no clue
    > >> where
    > >> to start. If someone can give me a little direction I should be able to
    > >> pull
    > >> things together from there.
    > >> Thank you in advance for your assistance.
    > >>

    >
    >
    >
     
    Ralph, Feb 8, 2008
    #5
  6. Why do you say your approach is more flexible than the one Albert proposed?

    Just as yours does, his code reads one line at a time until reaching the end
    of the file. The only difference I see is that he's using only built-in VBA
    functionality.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Ralph" <> wrote in message
    news:...
    > Yes I've seen the following, however I still like the flexibility. To each
    > his own.
    >
    > http://vbnet.mvps.org/index.html?code/fileapi/fsoapicompare.htm
    >
    > "Douglas J. Steele" wrote:
    >
    >> You could, but why introduce the overhead of FSO when it's not required?
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "Ralph" <> wrote in message
    >> news:...
    >> > You could also use FileSystemObject
    >> >
    >> > sub readTxtFile
    >> > dim fso
    >> > dim f
    >> > dim ts
    >> > dim strLine as string
    >> >
    >> > set fso=CreateObject("Scripting.FileSystemObject")
    >> > set f=fso.getfile("c:\temp\Test_20080206 LOG.TXT")
    >> > set ts=f.OpenAsTextStream
    >> >
    >> > 'loop through each line in text file
    >> > do while not ts.AtEndfOfLine
    >> > strLine=ts.readline
    >> > 'test for the line your looking for here
    >> > debug.print strline
    >> > loop
    >> >
    >> > ts.close
    >> > set fso=nothing
    >> > set f=nothing
    >> > set ts=nothing
    >> > end sub
    >> >
    >> > "AccessARS" wrote:
    >> >
    >> >> We have a proprietary system that generates daily processing logs in
    >> >> text
    >> >> files into a specific folder. These log files have a systematic
    >> >> naming
    >> >> convention i.e "Test_20080206 LOG.TXT", "Test_20080207 LOG.TXT"
    >> >> depending
    >> >> on
    >> >> the date. I am building a system in Access/VBA that will search for a
    >> >> line
    >> >> with a specific error message within the most current log file
    >> >> thereafter
    >> >> store that line item into a table for reporting purposes in Access.
    >> >>
    >> >> Example of error line in log text file:
    >> >> "09:51:37 I Processing stopped by user."
    >> >>
    >> >> I can identify the most recent file and point to the location on a
    >> >> daily
    >> >> basis in vba but can't seem to find any solid information on opening,
    >> >> searching and pulling text from the said text file. I've heard that
    >> >> there
    >> >> are methods for going through a text file line by line but have no
    >> >> clue
    >> >> where
    >> >> to start. If someone can give me a little direction I should be able
    >> >> to
    >> >> pull
    >> >> things together from there.
    >> >> Thank you in advance for your assistance.
    >> >>

    >>
    >>
    >>
     
    Douglas J. Steele, Feb 8, 2008
    #6
  7. AccessARS

    AccessARS Guest

    Yes, it has 5000 to 10000+lines. Thank you

    "Albert D. Kallal" wrote:

    > Does the file have "many" lines of text, or just one line of text??
    >
    > You can open a text file as follows:
    >
    > Sub ReadTextFile
    >
    > Dim strFile As String
    > Dim intF As Integer
    > Dim strLineBuf As String
    > Dim lngLines As Long
    > Dim lngBlank As Long
    >
    > strFile = "c:\my data\MyData.txt"
    >
    > intF = FreeFile()
    > Open strFile For Input As #intF
    >
    > Do While EOF(intF) = False
    > Line Input #intF, strLineBuf
    > If Trim(strLineBuf) = "" Then
    > lngBlank = lngBlank + 1
    > Else
    > lngLines = lngLines + 1
    > End If
    > Loop
    > Close intF
    >
    > End If
    >
    > MsgBox "Number non blank lines = " & lngLines & vbCrLf & _
    > "Blank lines = " & lngBlank & vbCrLf & _
    > "Total = " & lngBlank + lngLines
    >
    > End Function
    >
    > You can also use the split() command to parse out the one line if it is
    > delimonted...
    >
    > And, you could simply add to the above loop code add the one record to a
    > file
    >
    >
    > dim rstData as dao.RecordSet
    >
    > set rstData = currentdb.OpenRecordSet("tblMyData")
    > rstData.AddNew
    > rstData!FristName = "some text"
    > rstData!LastName = "some more text"
    > rstData.Update
    > rstData.Close
    >
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    >
    >
    >
    >
     
    AccessARS, Feb 8, 2008
    #7
  8. AccessARS

    Ralph Guest

    I said I liked the flexibility of FSO nowhere did I say that it was more
    flexible than the his solution, just an alternative.

    "Douglas J. Steele" wrote:

    > Why do you say your approach is more flexible than the one Albert proposed?
    >
    > Just as yours does, his code reads one line at a time until reaching the end
    > of the file. The only difference I see is that he's using only built-in VBA
    > functionality.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Ralph" <> wrote in message
    > news:...
    > > Yes I've seen the following, however I still like the flexibility. To each
    > > his own.
    > >
    > > http://vbnet.mvps.org/index.html?code/fileapi/fsoapicompare.htm
    > >
    > > "Douglas J. Steele" wrote:
    > >
    > >> You could, but why introduce the overhead of FSO when it's not required?
    > >>
    > >> --
    > >> Doug Steele, Microsoft Access MVP
    > >> http://I.Am/DougSteele
    > >> (no e-mails, please!)
    > >>
    > >>
    > >> "Ralph" <> wrote in message
    > >> news:...
    > >> > You could also use FileSystemObject
    > >> >
    > >> > sub readTxtFile
    > >> > dim fso
    > >> > dim f
    > >> > dim ts
    > >> > dim strLine as string
    > >> >
    > >> > set fso=CreateObject("Scripting.FileSystemObject")
    > >> > set f=fso.getfile("c:\temp\Test_20080206 LOG.TXT")
    > >> > set ts=f.OpenAsTextStream
    > >> >
    > >> > 'loop through each line in text file
    > >> > do while not ts.AtEndfOfLine
    > >> > strLine=ts.readline
    > >> > 'test for the line your looking for here
    > >> > debug.print strline
    > >> > loop
    > >> >
    > >> > ts.close
    > >> > set fso=nothing
    > >> > set f=nothing
    > >> > set ts=nothing
    > >> > end sub
    > >> >
    > >> > "AccessARS" wrote:
    > >> >
    > >> >> We have a proprietary system that generates daily processing logs in
    > >> >> text
    > >> >> files into a specific folder. These log files have a systematic
    > >> >> naming
    > >> >> convention i.e "Test_20080206 LOG.TXT", "Test_20080207 LOG.TXT"
    > >> >> depending
    > >> >> on
    > >> >> the date. I am building a system in Access/VBA that will search for a
    > >> >> line
    > >> >> with a specific error message within the most current log file
    > >> >> thereafter
    > >> >> store that line item into a table for reporting purposes in Access.
    > >> >>
    > >> >> Example of error line in log text file:
    > >> >> "09:51:37 I Processing stopped by user."
    > >> >>
    > >> >> I can identify the most recent file and point to the location on a
    > >> >> daily
    > >> >> basis in vba but can't seem to find any solid information on opening,
    > >> >> searching and pulling text from the said text file. I've heard that
    > >> >> there
    > >> >> are methods for going through a text file line by line but have no
    > >> >> clue
    > >> >> where
    > >> >> to start. If someone can give me a little direction I should be able
    > >> >> to
    > >> >> pull
    > >> >> things together from there.
    > >> >> Thank you in advance for your assistance.
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >
     
    Ralph, Feb 8, 2008
    #8
  9. AccessARS

    AccessARS Guest

    Thank you Albert... this is exactly the lead I needed.

    Once I find the line that I am searching for I need to also retrieve the
    information from another line which is usually
    "09:51:04 I Processing index line: ...."

    "Albert D. Kallal" wrote:

    > Does the file have "many" lines of text, or just one line of text??
    >
    > You can open a text file as follows:
    >
    > Sub ReadTextFile
    >
    > Dim strFile As String
    > Dim intF As Integer
    > Dim strLineBuf As String
    > Dim lngLines As Long
    > Dim lngBlank As Long
    >
    > strFile = "c:\my data\MyData.txt"
    >
    > intF = FreeFile()
    > Open strFile For Input As #intF
    >
    > Do While EOF(intF) = False
    > Line Input #intF, strLineBuf
    > If Trim(strLineBuf) = "" Then
    > lngBlank = lngBlank + 1
    > Else
    > lngLines = lngLines + 1
    > End If
    > Loop
    > Close intF
    >
    > End If
    >
    > MsgBox "Number non blank lines = " & lngLines & vbCrLf & _
    > "Blank lines = " & lngBlank & vbCrLf & _
    > "Total = " & lngBlank + lngLines
    >
    > End Function
    >
    > You can also use the split() command to parse out the one line if it is
    > delimonted...
    >
    > And, you could simply add to the above loop code add the one record to a
    > file
    >
    >
    > dim rstData as dao.RecordSet
    >
    > set rstData = currentdb.OpenRecordSet("tblMyData")
    > rstData.AddNew
    > rstData!FristName = "some text"
    > rstData!LastName = "some more text"
    > rstData.Update
    > rstData.Close
    >
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    >
    >
    >
    >
     
    AccessARS, Feb 8, 2008
    #9
  10. Personally, I like the code on the website Ralph referenced.

    Why in the world would you use built-in VBA functionality when you can have
    the complexity of Windows API calls or have the dependency on external
    objects like the FSO?

    --

    Sco

    M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005
    Denver Area Access Users Group Past President 2006/2007 www.DAAUG.org
    MS Colorado Events Administrator www.MSColoradoEvents.com
    This email made with 100% recycled electrons!
    Miscellaneous Access "stuff" at www.ScoBiz.com


    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%...
    > Why do you say your approach is more flexible than the one Albert
    > proposed?
    >
    > Just as yours does, his code reads one line at a time until reaching the
    > end of the file. The only difference I see is that he's using only
    > built-in VBA functionality.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Ralph" <> wrote in message
    > news:...
    >> Yes I've seen the following, however I still like the flexibility. To
    >> each
    >> his own.
    >>
    >> http://vbnet.mvps.org/index.html?code/fileapi/fsoapicompare.htm
    >>
    >> "Douglas J. Steele" wrote:
    >>
    >>> You could, but why introduce the overhead of FSO when it's not required?
    >>>
    >>> --
    >>> Doug Steele, Microsoft Access MVP
    >>> http://I.Am/DougSteele
    >>> (no e-mails, please!)
    >>>
    >>>
    >>> "Ralph" <> wrote in message
    >>> news:...
    >>> > You could also use FileSystemObject
    >>> >
    >>> > sub readTxtFile
    >>> > dim fso
    >>> > dim f
    >>> > dim ts
    >>> > dim strLine as string
    >>> >
    >>> > set fso=CreateObject("Scripting.FileSystemObject")
    >>> > set f=fso.getfile("c:\temp\Test_20080206 LOG.TXT")
    >>> > set ts=f.OpenAsTextStream
    >>> >
    >>> > 'loop through each line in text file
    >>> > do while not ts.AtEndfOfLine
    >>> > strLine=ts.readline
    >>> > 'test for the line your looking for here
    >>> > debug.print strline
    >>> > loop
    >>> >
    >>> > ts.close
    >>> > set fso=nothing
    >>> > set f=nothing
    >>> > set ts=nothing
    >>> > end sub
    >>> >
    >>> > "AccessARS" wrote:
    >>> >
    >>> >> We have a proprietary system that generates daily processing logs in
    >>> >> text
    >>> >> files into a specific folder. These log files have a systematic
    >>> >> naming
    >>> >> convention i.e "Test_20080206 LOG.TXT", "Test_20080207 LOG.TXT"
    >>> >> depending
    >>> >> on
    >>> >> the date. I am building a system in Access/VBA that will search for
    >>> >> a
    >>> >> line
    >>> >> with a specific error message within the most current log file
    >>> >> thereafter
    >>> >> store that line item into a table for reporting purposes in Access.
    >>> >>
    >>> >> Example of error line in log text file:
    >>> >> "09:51:37 I Processing stopped by user."
    >>> >>
    >>> >> I can identify the most recent file and point to the location on a
    >>> >> daily
    >>> >> basis in vba but can't seem to find any solid information on opening,
    >>> >> searching and pulling text from the said text file. I've heard that
    >>> >> there
    >>> >> are methods for going through a text file line by line but have no
    >>> >> clue
    >>> >> where
    >>> >> to start. If someone can give me a little direction I should be able
    >>> >> to
    >>> >> pull
    >>> >> things together from there.
    >>> >> Thank you in advance for your assistance.
    >>> >>
    >>>
    >>>
    >>>

    >
    >
     
    M.L. Sco Scofield, Feb 8, 2008
    #10
  11. You forgot the smiley, Sco! <g>

    See near the bottom of http://my.advisor.com/articles.nsf/aid/16279

    "As you can see, in finding 5491 files on my hard drive, the API approach
    was about 10 times faster than the Dir function (.191 seconds vs. 1.222
    seconds), while the Dir function was about 10 times faster than the FSO
    approaches (1.222 seconds vs. 10.285 or 10.295 seconds). When I modified the
    routines to look for more than simply MP3 files, this relationship was
    repeated."


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "M.L. Sco Scofield" <Sco at ScoBiz dot com> wrote in message
    news:%23py$...
    > Personally, I like the code on the website Ralph referenced.
    >
    > Why in the world would you use built-in VBA functionality when you can
    > have the complexity of Windows API calls or have the dependency on
    > external objects like the FSO?
    >
    > --
    >
    > Sco
    >
    > M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005
    > Denver Area Access Users Group Past President 2006/2007 www.DAAUG.org
    > MS Colorado Events Administrator www.MSColoradoEvents.com
    > This email made with 100% recycled electrons!
    > Miscellaneous Access "stuff" at www.ScoBiz.com
    >
    >
    > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:%...
    >> Why do you say your approach is more flexible than the one Albert
    >> proposed?
    >>
    >> Just as yours does, his code reads one line at a time until reaching the
    >> end of the file. The only difference I see is that he's using only
    >> built-in VBA functionality.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "Ralph" <> wrote in message
    >> news:...
    >>> Yes I've seen the following, however I still like the flexibility. To
    >>> each
    >>> his own.
    >>>
    >>> http://vbnet.mvps.org/index.html?code/fileapi/fsoapicompare.htm
    >>>
    >>> "Douglas J. Steele" wrote:
    >>>
    >>>> You could, but why introduce the overhead of FSO when it's not
    >>>> required?
    >>>>
    >>>> --
    >>>> Doug Steele, Microsoft Access MVP
    >>>> http://I.Am/DougSteele
    >>>> (no e-mails, please!)
    >>>>
    >>>>
    >>>> "Ralph" <> wrote in message
    >>>> news:...
    >>>> > You could also use FileSystemObject
    >>>> >
    >>>> > sub readTxtFile
    >>>> > dim fso
    >>>> > dim f
    >>>> > dim ts
    >>>> > dim strLine as string
    >>>> >
    >>>> > set fso=CreateObject("Scripting.FileSystemObject")
    >>>> > set f=fso.getfile("c:\temp\Test_20080206 LOG.TXT")
    >>>> > set ts=f.OpenAsTextStream
    >>>> >
    >>>> > 'loop through each line in text file
    >>>> > do while not ts.AtEndfOfLine
    >>>> > strLine=ts.readline
    >>>> > 'test for the line your looking for here
    >>>> > debug.print strline
    >>>> > loop
    >>>> >
    >>>> > ts.close
    >>>> > set fso=nothing
    >>>> > set f=nothing
    >>>> > set ts=nothing
    >>>> > end sub
    >>>> >
    >>>> > "AccessARS" wrote:
    >>>> >
    >>>> >> We have a proprietary system that generates daily processing logs in
    >>>> >> text
    >>>> >> files into a specific folder. These log files have a systematic
    >>>> >> naming
    >>>> >> convention i.e "Test_20080206 LOG.TXT", "Test_20080207 LOG.TXT"
    >>>> >> depending
    >>>> >> on
    >>>> >> the date. I am building a system in Access/VBA that will search for
    >>>> >> a
    >>>> >> line
    >>>> >> with a specific error message within the most current log file
    >>>> >> thereafter
    >>>> >> store that line item into a table for reporting purposes in Access.
    >>>> >>
    >>>> >> Example of error line in log text file:
    >>>> >> "09:51:37 I Processing stopped by user."
    >>>> >>
    >>>> >> I can identify the most recent file and point to the location on a
    >>>> >> daily
    >>>> >> basis in vba but can't seem to find any solid information on
    >>>> >> opening,
    >>>> >> searching and pulling text from the said text file. I've heard that
    >>>> >> there
    >>>> >> are methods for going through a text file line by line but have no
    >>>> >> clue
    >>>> >> where
    >>>> >> to start. If someone can give me a little direction I should be
    >>>> >> able to
    >>>> >> pull
    >>>> >> things together from there.
    >>>> >> Thank you in advance for your assistance.
    >>>> >>
    >>>>
    >>>>
    >>>>

    >>
    >>

    >
    >
     
    Douglas J. Steele, Feb 9, 2008
    #11
  12. Smiley? I was serious Doug. I bill by the hour. The more code I write, the
    longer it takes. The longer it takes, the more I bill. API calls take the
    most code hence maximizing billings.

    The downside of API calls is that they reduce library dependences. Trouble
    shooting missing references has always been a great billing padder.

    <g,d,&r>

    Sco

    M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005
    Denver Area Access Users Group Past President 2006/2007 www.DAAUG.org
    MS Colorado Events Administrator www.MSColoradoEvents.com
    This email made with 100% recycled electrons!
    Miscellaneous Access "stuff" at www.ScoBiz.com


    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:O7U$...
    > You forgot the smiley, Sco! <g>
    >
    > See near the bottom of http://my.advisor.com/articles.nsf/aid/16279
    >
    > "As you can see, in finding 5491 files on my hard drive, the API approach
    > was about 10 times faster than the Dir function (.191 seconds vs. 1.222
    > seconds), while the Dir function was about 10 times faster than the FSO
    > approaches (1.222 seconds vs. 10.285 or 10.295 seconds). When I modified
    > the routines to look for more than simply MP3 files, this relationship was
    > repeated."
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "M.L. Sco Scofield" <Sco at ScoBiz dot com> wrote in message
    > news:%23py$...
    >> Personally, I like the code on the website Ralph referenced.
    >>
    >> Why in the world would you use built-in VBA functionality when you can
    >> have the complexity of Windows API calls or have the dependency on
    >> external objects like the FSO?
    >>
    >> --
    >>
    >> Sco
    >>
    >> M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005
    >> Denver Area Access Users Group Past President 2006/2007 www.DAAUG.org
    >> MS Colorado Events Administrator www.MSColoradoEvents.com
    >> This email made with 100% recycled electrons!
    >> Miscellaneous Access "stuff" at www.ScoBiz.com
    >>
    >>
    >> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    >> news:%...
    >>> Why do you say your approach is more flexible than the one Albert
    >>> proposed?
    >>>
    >>> Just as yours does, his code reads one line at a time until reaching the
    >>> end of the file. The only difference I see is that he's using only
    >>> built-in VBA functionality.
    >>>
    >>> --
    >>> Doug Steele, Microsoft Access MVP
    >>> http://I.Am/DougSteele
    >>> (no e-mails, please!)
    >>>
    >>>
    >>> "Ralph" <> wrote in message
    >>> news:...
    >>>> Yes I've seen the following, however I still like the flexibility. To
    >>>> each
    >>>> his own.
    >>>>
    >>>> http://vbnet.mvps.org/index.html?code/fileapi/fsoapicompare.htm
    >>>>
    >>>> "Douglas J. Steele" wrote:
    >>>>
    >>>>> You could, but why introduce the overhead of FSO when it's not
    >>>>> required?
    >>>>>
    >>>>> --
    >>>>> Doug Steele, Microsoft Access MVP
    >>>>> http://I.Am/DougSteele
    >>>>> (no e-mails, please!)
    >>>>>
    >>>>>
    >>>>> "Ralph" <> wrote in message
    >>>>> news:...
    >>>>> > You could also use FileSystemObject
    >>>>> >
    >>>>> > sub readTxtFile
    >>>>> > dim fso
    >>>>> > dim f
    >>>>> > dim ts
    >>>>> > dim strLine as string
    >>>>> >
    >>>>> > set fso=CreateObject("Scripting.FileSystemObject")
    >>>>> > set f=fso.getfile("c:\temp\Test_20080206 LOG.TXT")
    >>>>> > set ts=f.OpenAsTextStream
    >>>>> >
    >>>>> > 'loop through each line in text file
    >>>>> > do while not ts.AtEndfOfLine
    >>>>> > strLine=ts.readline
    >>>>> > 'test for the line your looking for here
    >>>>> > debug.print strline
    >>>>> > loop
    >>>>> >
    >>>>> > ts.close
    >>>>> > set fso=nothing
    >>>>> > set f=nothing
    >>>>> > set ts=nothing
    >>>>> > end sub
    >>>>> >
    >>>>> > "AccessARS" wrote:
    >>>>> >
    >>>>> >> We have a proprietary system that generates daily processing logs
    >>>>> >> in text
    >>>>> >> files into a specific folder. These log files have a systematic
    >>>>> >> naming
    >>>>> >> convention i.e "Test_20080206 LOG.TXT", "Test_20080207 LOG.TXT"
    >>>>> >> depending
    >>>>> >> on
    >>>>> >> the date. I am building a system in Access/VBA that will search
    >>>>> >> for a
    >>>>> >> line
    >>>>> >> with a specific error message within the most current log file
    >>>>> >> thereafter
    >>>>> >> store that line item into a table for reporting purposes in Access.
    >>>>> >>
    >>>>> >> Example of error line in log text file:
    >>>>> >> "09:51:37 I Processing stopped by user."
    >>>>> >>
    >>>>> >> I can identify the most recent file and point to the location on a
    >>>>> >> daily
    >>>>> >> basis in vba but can't seem to find any solid information on
    >>>>> >> opening,
    >>>>> >> searching and pulling text from the said text file. I've heard
    >>>>> >> that
    >>>>> >> there
    >>>>> >> are methods for going through a text file line by line but have no
    >>>>> >> clue
    >>>>> >> where
    >>>>> >> to start. If someone can give me a little direction I should be
    >>>>> >> able to
    >>>>> >> pull
    >>>>> >> things together from there.
    >>>>> >> Thank you in advance for your assistance.
    >>>>> >>
    >>>>>
    >>>>>
    >>>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    M.L. Sco Scofield, Feb 9, 2008
    #12
  13. OT (was: Find/retrieve text from a .txt file through Access/VBA)

    Not sure why I'd want to talk to you, since you never answered my email
    telling you I was passing through Denver! <g>

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "M.L. Sco Scofield" <Sco at ScoBiz dot com> wrote in message
    news:...
    > Smiley? I was serious Doug. I bill by the hour. The more code I write, the
    > longer it takes. The longer it takes, the more I bill. API calls take the
    > most code hence maximizing billings.
    >
    > The downside of API calls is that they reduce library dependences. Trouble
    > shooting missing references has always been a great billing padder.
    >
    > <g,d,&r>
    >
    > Sco
     
    Douglas J. Steele, Feb 9, 2008
    #13
  14. Re: OT (was: Find/retrieve text from a .txt file through Access/VBA)

    You mean the one from 11/14/06 where you said you were passing through and
    wouldn't be here long enough to even call? It's in my "Important -
    Follow-up" folder. Was going to answer it after I finished a class. Dang,
    time flies when you're havin' fun...

    Sco

    M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005
    Denver Area Access Users Group Past President 2006/2007 www.DAAUG.org
    MS Colorado Events Administrator www.MSColoradoEvents.com
    This email made with 100% recycled electrons!
    Miscellaneous Access "stuff" at www.ScoBiz.com


    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:u$...
    > Not sure why I'd want to talk to you, since you never answered my email
    > telling you I was passing through Denver! <g>
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "M.L. Sco Scofield" <Sco at ScoBiz dot com> wrote in message
    > news:...
    >> Smiley? I was serious Doug. I bill by the hour. The more code I write,
    >> the longer it takes. The longer it takes, the more I bill. API calls take
    >> the most code hence maximizing billings.
    >>
    >> The downside of API calls is that they reduce library dependences.
    >> Trouble shooting missing references has always been a great billing
    >> padder.
    >>
    >> <g,d,&r>
    >>
    >> Sco

    >
    >
     
    M.L. Sco Scofield, Feb 10, 2008
    #14
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alex Dybenko

    Re: How do I use VBA to retrieve an Access DBs file properties?

    Alex Dybenko, Sep 1, 2004, in forum: Microsoft Access VBA Modules
    Replies:
    7
    Views:
    334
    patar
    Apr 1, 2008
  2. Guest

    Pls Hlp! How to convert a .xls file to .txt file in Access / VB

    Guest, Nov 22, 2004, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    228
    John Nurick
    Nov 22, 2004
  3. AccessARS

    Find retrieve text from a text file through VBA

    AccessARS, Feb 8, 2008, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    197
    AccessARS
    Feb 8, 2008
  4. lena_form

    VBA code importe txt file

    lena_form, Feb 26, 2010, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    179
    ryguy7272
    Feb 26, 2010
  5. Michael

    Retrieve an External File, pdf, txt, jpeg... etc

    Michael, Apr 1, 2010, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    318
    Michael
    Apr 1, 2010
Loading...

Share This Page