File Not Found--simple VBA code

[Tranman]

Hi Guys,
I have a really simple piece of code that concatenates a bunch of CSV files into a single file which I then manipulate in Excel then import into Access. The code is in an Access Sub which has worked in the past, but today, it will not work at all...gives "Error 53 File Not Found" on the flagged line.

I have tried:
Renaming the files.
Saving the module.
Creating a new module and pasting the code into it.
Creating a new MDB and a new module.
Renaming the sub.
Compacting and repairing the MDB.
Rebooting the system.
Verified that the file name returned by the initial Dir exists.

Would tear my hair out but it's already gone...

Oh yeah--WinXP Pro, Office XP Pro



Here's the code:

Private Sub Concat()
Dim strInFile As String
Dim strOutFile As String
Dim strIn As String
Dim intRowCount As Integer
strOutFile = "c:\trafseqacc\csvfilesin\emptyfile.csv"
strInFile = Dir("c:\trafseqacc\csvfilesin\SRAD*.CSV")
Open strOutFile For Output As #1

Do Until strInFile = ""
Open strInFile For Input As #2 ' ***** ERROR ON THIS LINE

Do While Not EOF(2)
intRowCount = intRowCount + 1
Line Input #2, strIn
Print #1, strIn
Loop

Close #2
strInFile = Dir()
Loop

Close #1
End Sub

Any ideas?

Tranman

 

[john2thompson]

I don't really have much experience with using files in this way in VBA, but could it be because ou are using the wildcard character (*). If there is more that one file that starts with SRAD then it could cause a problem?
Alternatively, it could be looking for a file literally called SRAD*.CSV which might not be present.

As I say, I dont have experience in this area, but just a suggestion.

cheers,
John

 

[Tranman]

Thanks John.

No, the code is correct. The name of the file it is trying to open is in strInFile "SRAD_I0003500_HIST_TRAF_COUNT.CSV" and the file really does exist.

Since my ititial post, I tried pasting the code into an Excel module--doesn't work there, either.

Also pasted the code into a VB6 program. Fails there, too.

Apparently an XP/Security problem?

 

[NeverBen]

Hi Tranman,
The Dir() statement will strip off the path and return just the filename. In order to open the file, you need the full path.

 

[Tranman]

By Jove, you're right. Tricked by the ole Path...

Thanks.