Results 1 to 3 of 3
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    St Louis, Missouri, USA
    Thanked 1 Time in 1 Post

    Random Access of TxtFile (VBA/Excel/XP)

    I have an Excel file with 1 to 200 rows of data. Using 3 criteria from that file, I need to go to a textfile with 28K rows of data, match the 3 criteria, then pick up additional information from the text file for further processing.

    I have experience finding data in a text file when it is fairly sequential, but this mess seems to be somewhat haphazard (actually sorted on a field that I do not use in my query).

    Anybody got any thoughts? <img src=/S/confused.gif border=0 alt=confused width=15 height=20> I have never done Random Access of a text file.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 94 Times in 90 Posts

    Re: Random Access of TxtFile (VBA/Excel/XP)

    Is the text file delimited in a useful manner such as CSV? If so, my suggestion would be to try ADO. ADO allows you to address as CSV file (or, using a schema.ini file, other types of delimited files) as a table. The technique is more or less as follows:

    1. <LI>Set a Reference to Microsoft ActiveX Data Objects 2.x Library (the version will vary, but it should correspond to msado15.dll on the Location line at the bottom of the dialog).

      <LI>Use code such as the following, which uses the ODBC driver for text files under the covers:
      <pre>Dim cn As New ADODB.Connection
      cn.Open "PROVIDER=MSDASQL;" & _
      "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _

      <LI>You then can simply open the CSV file as a table into an ADO Recordset:
      <pre>Dim rs As New ADODB.Recordset
      rs.Open "hugefile.txt", cn, adOpenDynamic, , adCmdTable</pre>
    Please note that about half of that came from a book, untested, straight to you.

    You then could Filter on the table. Or is it Find? I haven't had to do this, thankfully, so I'm not sure which method would give you the best results.Hope this helps. Please let us know if there are any errors in the above.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Brussels, Brussel, Belgium
    Thanked 0 Times in 0 Posts

    Re: Random Access of TxtFile (VBA/Excel/XP)

    here's a kb that refers to what you want to do:;EN-US;230265

Posting Permissions

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