Open a txt file w/vba

[Guest]

I am trying to open a text file, convert data in the file and then output it
to another text file before I import it to Access.

I keep getting a runtime error '52' Bad file name or number using the
following filename. Only thing I can think of is my database is not on the
same drive as these files are. Or should this be a function? I am not very
well trained in vba so any help is appreciated.

Public Sub OpenTextFile()
Open "Q:\ScanSrs\Database\2007\Oper Reports\PUMSSUMOPER2.TXT" For Input As
#fIn
Open "Q:\ScanSrs\Database\2007\Oper Reports\PUMSSUMOPER3.TXT" For Output As
#fOut

 

[Ken Snell \(MVP\)]

Dim fIn As Integer, fOut As Integer
fIn = FreeFile()
fOut = FreeFile()
Open "Q:\ScanSrs\Database\2007\Oper Reports\PUMSSUMOPER2.TXT" For Input As
#fIn
Open "Q:\ScanSrs\Database\2007\Oper Reports\PUMSSUMOPER3.TXT" For Output As
#fOut

 

[Guest]

Thanks, now I am getting a runtime error '55' "file already open" for the
output file. I don't have the file open and am not using the file for any
other macros or modules.

 

[Brendan Reynolds]

The following small change should fix that ...

Dim fIn As Integer, fOut As Integer
fIn = FreeFile()
Open "Q:\ScanSrs\Database\2007\Oper Reports\PUMSSUMOPER2.TXT" For Input As
#fIn
fOut = FreeFile()
Open "Q:\ScanSrs\Database\2007\Oper Reports\PUMSSUMOPER3.TXT" For Output As
#fOut

The original code had the second call to FreeFile *before* the first Open
statement, which would result in the same value being stored in both fIn and
fOut, which in turn would result in the two Open statements attempting to
use the same file handle.

 

[Ken Snell \(MVP\)]

Thanks, Brendan. My error.

 

[Guest]

Thank you both gentlemen, that worked. I am sure I will have more questions
for you.