1 Replies - 3030 Views - Last Post: 29 April 2010 - 02:49 AM

#1 Guest_Arthur*


Reputation:

Access VBA need help reading all text files in a folder

Posted 29 April 2010 - 01:08 AM

Hello Folks,

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



Any advice?

Thanks

Arthur

Is This A Good Question/Topic? 0

Replies To: Access VBA need help reading all text files in a folder

#2 HiArt  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 14-April 08

Re: Access VBA need help reading all text files in a folder

Posted 29 April 2010 - 02:49 AM

Sorted.

Amended
Set fileObj = fso.GetFile(f.Name)
to
Set fileObj = fso.GetFile(path & f.Name)

Secondly, the first file was corrupt. Once removed this worked perfectly.

Art
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1