Reading from Text Files 1

[DominicG]

I am looking to read some information from a fixed width text file. Using VBA in Access i want to put an if statement depending on the first two characters. Then I want to change one of the characters.

can anyone point me in teh right direction and perhaps supply me with some sample code.

 

[michaelbr55]

Hi DominicG
This might get you going in the right direction, it's not from Access but Excel, I haven't done any programming in Access, but as you're only playing around with text files
it shouldn't mattter.



Sub ReadText()
Dim fnum, variable1, x, TextLine(5000), Your_First_Character, New_Character, New_Character2, Your_Second_Character 'declare variables
Your_First_Character = Chr(34) ' ie inverted comma's "
Your_Second_Character = "E"
New_Character = "@"
New_Character2 = "$"
fnum = FreeFile 'assign freefile number to fnum
Open "c:\temp\textfile.txt" For Input As fnum ' open text file
Do While Not EOF(fnum) ' loop to end of file
variable1 = variable1 + 1 ' counter
Line Input #1, TextLine(variable1) 'assign each line of the text file to a variable array
Loop
Close ' text file

For x = 1 To variable1 ' count up to the number of lines in the text file
If Left(TextLine(x), 1) = Your_First_Character Then ' if it finds your character in 1st place
TextLine(x) = New_Character & Right(TextLine(x), Len(TextLine(x)) - 1) 'replaces that character with "New_Character"
End If
' you may have to elseif here depending on what you want it to do
If Mid(TextLine(x), 2, 1) = Your_Second_Character Then ' if it finds your second character in 2nd place
TextLine(x) = Left(TextLine(x), 1) & New_Character2 & Right(TextLine(x), Len(TextLine(x)) - 2) 'replaces it with "New_Character2"
End If
Next

Open "c:\temp\textfile.txt" For Output As fnum
For x = 1 To variable1
Print #fnum, TextLine(x) 'writes the new lines back to textfile.txt
Next
Close

End Sub

Hope this helps

 

[DominicG]

Many thanks michaelbr55 I will give it a go.