I am attempting to read all the text files in to a folder using Access VBA. I need to open and read these as some pre-processing is required before writing the data to the appropriate tables.
The following code does not work, but I do not understand why not. It opens the first file but the "While Not ts.AtEndOfStream" immediuatly returns true, even though the file is far from empty, hence no data in the file is read.
Secondly, I get a "file not found" error at the next attempt to execute "Set fileObj = fso.GetFile(f.Name)", even though it is the FileScriptingObject that has returned the filename!
I have been all over the place with this and gave attempted "LINE INPUT XXX AS F" type structure too.
here is the errant code:
Public Sub ImportData() Dim db As Database Dim rsRace As Recordset Dim rsHorse As Recordset Dim fileNumber% Dim sBuf As String Dim fs As String Dim fso Dim fld Dim fl Dim f Dim ts Dim fileObj Const ForReading = 1, ForWriting = 2, ForAppending = 8 Set db = CurrentDb() Set fso = CreateObject("Scripting.FileSystemObject") sSql = "SELECT *" & _ " FROM _Race;" Set rsRace = db.OpenRecordset(sSql) sSql = "SELECT *" & _ " FROM _Horse;" Set rsHorse = db.OpenRecordset(sSql) Set fld = fso.GetFolder("C:\Temp\rpdata") Set fl = fld.Files For Each f In fl Set fileObj = fso.GetFile(f.Name) Set ts = fileObj.OpenAsTextStream(ForReading, TristateUseDefault) Debug.Print ("File: " & f.Name) While Not ts.AtEndOfStream Debug.Print ts.ReadLine Wend ts.Close Next 'Close stuff rsRace.Close rsHorse.Close 'Clean up Set rsRace = Nothing Set rsHorse = Nothing Set db = Nothing End Sub