Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Read text (Word 2000)

    I have a text file that I use Word to open & manipulate. The third line has a date I want to capture and use. How do I tell VBA "OrderDate = Right(Line3,10)"?

    <pre>Line1........
    Line2........
    CODE PRODUCT TOT UNITS RACKS TRAYS UNITS CODE 05/03/2007</pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read text (Word 2000)

    Like this:
    <code>
    Dim f As Integer
    Dim strLine As String
    Dim OrderDate As Date
    ' First available file number
    f = FreeFile
    ' Open the file - substute correct path and filename
    Open "Test.txt" For Input As #f
    ' Read 3 lines, ignore the first two
    Line Input #f, strLine
    Line Input #f, strLine
    Line Input #f, strLine
    ' Extract last part
    OrderDate = Right(strLine, 10)
    ' Do something with the variable, e.g.
    Debug.Print OrderDate
    ' Close the file
    Close #f</code>

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Read text (Word 2000)

    Or just as an alternative, using the FileSystemObject:

    <pre> Dim avarData, strFileName As String
    Dim fso As Object, tsrStream1 As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    strFileName = "C:Testread.txt"
    Set tsrStream1 = fso.OpenTextFile(strFileName, ForReading, False)
    avarData = tsrStream1.ReadAll
    avarData = Split(avarData, vbCrLf)
    Debug.Print Right(avarData(2), 10)
    tsrStream1.Close
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Read text (Word 2000)

    Both solutions seem to imply the file needs to be opened by the routine in order to read it. In the situation I am working with the file is already open. Would these work under this circumstance? Or, should I close the file, read it with your routine to extract the date, then re-open and continue my processing?

  5. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Read text (Word 2000)

    If you already have the file open then you can avoid a large chunk of the shown code and work with the string...
    ActiveDocument.Paragraphs(3).Range.Text
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Read text (Word 2000)

    Was the file opened by your code and, if so, how? Or was it already open by the user?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Read text (Word 2000)

    My code opens the file to inset page breaks, then save & archive the source and converted files. When archiving I put a datestamp on the file. Currently unable to read the date from the file itself I use the date the process is run:<pre>'Save the file as a Word document
    ActiveDocument.SaveAs FileName:="DOLLY.DOC", FileFormat:=wdFormatDocument
    ActiveDocument.Close

    'Copy the converted file to a date-stamped archive file
    FileCopy HomePath & "Dolly.doc", HomePath & "ArchiveConvertedDolly " _
    & Format(Now(), "YYMMDD") & ".doc"</pre>

    There are two options here then, as I see it. I could capture the OrderDate value from the open file before the 'ActiveDocument.Close' command, or let the file close and read the date from the closed file (variable name = SourceFile), and then in either case use OrderDate in place of 'Format(Now(), "YYMMDD").

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Read text (Word 2000)

    Since you have the file open in Word anyway, I would use Andrew Lockton's method to read the order date.

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Read text (Word 2000)

    Got it working, thanks. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> I just had to tweak a little to strip non-printing characters off the end before I could pull the date from the line.<pre>'Grab OrderDate
    OrderDateLine = Trim(ActiveDocument.Paragraphs(3).Range.Text)
    OrderDateLine = Left(OrderDateLine, Len(OrderDateLine) - 2) 'strip CR/LF
    OrderDate = DateValue(Right(OrderDateLine, 10))
    OrderDateStr = Format(OrderDate, "YYMMDD")</pre>


Posting Permissions

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