Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Writing to and retreiving data from Text files (VBA/Word/2000 & 2003)

    I have a need to store data on disk and have been experimenting with the Write # and Input # commands.

    The 6th record created by WriteTest (code is provided below), is provided here:
    <hr>6,"First Variable 6","2nd Variable 56"<hr>
    In my testing I note that the data is returned with double sets of quotation marks around the text and single sets around numeric values.

    The code I have developed for this testing is as follows: <pre>Option Explicit

    Sub WriteTest()

    Dim i As Long

    Open "Pathname" For Output As #1
    For i = 1 To 10
    Write #1, i, "First Variable " & i, "2nd Variable " & 50 + i
    Next
    Close #1
    End Sub
    '````````````````````````````````````````````````` `````````````````````````
    Sub ReadTest()
    Dim Field As Long
    Dim myRecords As Long
    Dim RecLeng As Long
    Dim newline As Variant
    Dim StPt As Long
    Dim FoundPt As Long
    Dim RecNo As Long
    Dim FieldStr As String

    ReDim text(2, 0)

    Open "Pathname" For Input As #1
    Do Until EOF(1)
    Line Input #1, newline
    Field = -1
    StPt = 1
    FoundPt = InStr(StPt, newline, ",")
    Do Until FoundPt = 0
    Field = Field + 1
    If Field = 0 Then
    FieldStr = Mid(newline, StPt, FoundPt - StPt)
    Else
    FieldStr = Mid(newline, StPt + 1, FoundPt - StPt - 2)
    End If
    '##############################
    ' Do something with the data
    '##############################
    StPt = FoundPt + 1
    FoundPt = InStr(StPt, newline, ",")
    Loop
    FieldStr = Mid(newline, StPt + 1, Len(newline) - StPt - 1)
    RecLeng = Len(newline)
    RecNo = RecNo + 1
    Loop
    Close #1
    End Sub
    </pre>


    I believe the foregoing ReadTest procedure is overly complicated. There must be a simpler manner of extracting the data.
    As always, any guidance will be appreciated.
    T.I.A.
    Regards
    Don

  2. #2
    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: Writing to and retreiving data from Text file

    <P ID="edit" class=small>(Edited by jscher2000 on 21-Mar-06 22:52. )</P>Please excuse the confusion of my earlier reply...

    I have had luck using Print and Line Input to write and read text file data. I don't need the quote-wrapping-of-strings feature of the Write command.

    I also like the TextStream object (part of the Scripting Library's FileSystemObject), but it involves more overhead to use than the built-in native VB commands.

    Since you are using Word, if you have only a few parameters, you could consider using PrivateProfileString and an .INI file.

    Finally, to be fully modern and grossly inefficient, you could use the MSXML library and store your data in an XML file. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Added: I generally like to pack data of a known layout into a | (vertical bar) delimited string and then use Split to unpack it to an array. So in your case, you could write/print:

    i & "|" & 50+i

    and retrieve the values using

    dim intArray() as integer
    '...
    intArray = Split(retrieved string, "|")
    'do something with the array elements...

    But if you need that field name info in there, this isn't applicable.

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

    Re: Writing to and retreiving data from Text files (VBA/Word/2000 & 2003)

    You can use Input instead of Line Input:

    Dim Field1 As Integer
    Dim Field2 As String
    Dim Field3 As String
    ...
    Do Until EOF(1)
    Input #1, Field1, Field2, Field3
    ...

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing to and retreiving data from Text file

    Jefferson
    Thank you for the explanations. I can see the value of your vertical bar, Split approach. I expect that I will make use of it many times.
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing to and retreiving data from Text files (VBA/Word/2000 & 2003)

    Thank you Hans
    As always, you hit the nail squarely on the head.
    Regards
    Don

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing to and retreiving data from Text files (VBA/Word/2000 & 2003)

    In manipulating objects as a consequence of the information read from the text files, I am finding it necessary to revise the text file by removing and inserting records. My current approach is to:
    1. <LI>Open the text file for Input,
      <LI>read the file as you have recommended,
      <LI>using the data populate any number of arrays
      <LI>close the text file
      <LI>conduct the operations indicated by analysis of the data stored in the arrays,
      <LI>open the text file for output
      <LI>write the complete file as appropriate
      <LI>close the text file
    This approach has been taken more on gut feel than absolute knowledge. So my question boils down to: Is there a more efficient manner in carrying out these operations?
    T.I.A.
    Regards
    Don

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

    Re: Writing to and retreiving data from Text files (VBA/Word/2000 & 2003)

    In general, this looks like a fine approach. Without knowing how the data are manipulated, it is impossible to say if it is the most efficient way.

    For example, if you look for a single data item in the text file and replace that, it might be more efficient to open the text file in Word itself (as a document), and to use Word's Find method to locate the data you're looking for.

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing to and retreiving data from Text files (VBA/Word/2000 & 2003)

    Thank you Hans
    Regards
    Don

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing to and retreiving data from Text file

    I do this with my large-string INI files, storing values longer than MS's 255-char limit.
    I grab the entite file as a single string, parse to the section & key & value, Split, then re-assemble. Using Instr to parse directly to the section seems to me a great deal faster & simpler than filling an array.

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Writing to and retreiving data from Text file

    Chris
    Thanks for the insight.
    Regards
    Don

Posting Permissions

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