Looping through a directory

[Moxy1]

I'm trying to loop through a directory based on locations and file names that are in a table. Everytime I run the code I get a bad file name/count when trying to set strFileName. The files do exist and will work if I hardcode these values into the code. Any help would be apprectiated.

Function test()

Dim recFileImport As Recordset
Dim strFileImport As String
Dim strLocation As String
Dim strExtention As String
Dim strPrefix As String
Dim strSpec As String
Dim strTable As String
Dim strLayout As String
Dim strFileName As Variant

strFileImport = "SELECT * FROM tblFileImport;"
Set recFileImport = CurrentDb.OpenRecordset(strFileImport)

    With recFileImport
        Do Until .EOF
            strLocation = ![fli_location]
            strExtention = ![fli_extention]
            strPrefix = ![fli_prefix]
            strSpec = ![fli_spec]
            strTable = ![fli_table]
            strLayout = ![fli_tran_type]
            'strLocation = "C:\My Documents\"
            
            
            strFileName = Dir$(strLocation & strExtention)
           While strFileName <> ""
                DoCmd.TransferText strLayout, strSpec, strTable, strFileName
                strFileName = Dir$()
            
            Wend
            
            .MoveNext
        Loop
    End With

End Function

 

[EBGreen]

Does your extension field or your location field include the . that should go in there when they are combined?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Moxy1]

yes. Here are the values I have to the fields in question:

strExtention: .csv
strLocation: C:\My Documents\*

I have tried moving the * between the 2 fields with no luck.

 

[EBGreen]

Nothing leaps out at me as a syntax issue so I would look at the data. Make sure that there isn't any white space or hidden characters lurking around causing issues.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Remou]

Is it possible you are getting a shortcut returned/ These can be a problem.

 

[PHV]

Replace this:
DoCmd.TransferText strLayout, strSpec, strTable, strFileName
with this:
DoCmd.TransferText strLayout, strSpec, strTable, Replace(strLocation, "*", strFileName)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[traingamer]

Or with the * on the extension (*.csv)

DoCmd.TransferText strLayout, strSpec, strTable, strLocation & strFileName

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill