Editing a txt file before using docmd.transfertext

islman1975 · Dec 22, 2004

I've got a txt file I want to import using the Docmd.importtext but I first need to delete all the text up to but not including the following text string 'TyprRI'.
If I do this manually i can get it to work but I need to do it from VBA as I get the user to browse to the original text file from a form.
I've attched the two text files. The first is the original file and the second is what I need to alter the text file into and then save it. I've got a s far as opening the text file but have no idea how to go about parsing the text from the start of the file upto the test string 'TypreRI'.

WayneRyan · Dec 22, 2004

Islman,

I'm assuming that you have successfully browsed for the file and
put it in a form control called "myFile".

The following will DELETE the original file and switch the TEMP
file into its place.

Back up your files prior to using the code!
===========================================

Code:

Dim buffer As String

Open Me.MyFile For Input As #1
Open "C:\Temp.txt" For Output As #2

Line Input #1, buffer
While Not EOF(1) And InStr(1, buffer, "TypeRI") = 0
   If EOF(1) Then
      MsgBox("Invalid File.")
      Close #1
      Close #2
      Exit Sub
   End If
   Line Input #1, buffer
   Wend

While Not EOF(1)
   Print #2, buffer
   Line Input #2, buffer
   Wend

Close #1
Close #2

Kill Me.MyFile
Name "C:\Temp.txt" As Me.MyFile

MsgBox("Done.")

Untested, but that's the general idea.

Wayne

john471 · Dec 23, 2004

Wayne, as always, very generous effort.

Islman, don't know how familiar you are with these things, but in case it is not apparent to you, you will need to fix the typo in Wayne's second while/wend structure. After Print #2, buffer, you need Line Input #1, buffer

Also, I'm not certain if the code will include the final line of the file. You may want to check that, and add an extra Print #2, buffer outside the loop if necessary.

HTH

Regards

John.

WayneRyan · Dec 23, 2004

John,

Nice catch!

Merry Christmas to you and Islman.

Wayne

Editing a txt file before using docmd.transfertext

islman1975

Guest

Attachments

WayneRyan

AWF VIP

john471

Registered User.

WayneRyan

AWF VIP

Similar threads

Users who are viewing this thread