Dir() issues

[SQLJoe]

I need to select only the files in a directory where the first 9 characters in the file name match the values in a recordset, and copy these files to another directory. I've tried using two Dir()'s in short sequence (one inside a nested loop) to use alternately for the source folder and the destination folder, but that didn't go so well (any examples of how to do this would be greatly appreciated).

Now I am trying to add the full file names to an array when the above match with the recordset is made, then later use a dir to copy the files over in a separate block of code altogether (hoping that works).

I'm not used to using Dir() and I'm getting some unexpected behavior. After my code correctly adds the first matching file name to the array, the variable strCurrentFile correctly changes to the next file name in the directory when this line of code fires:

strCurrentFile = Dir(strSourcePath & "*.*")

but then strangely changes back to the previous value when the next line fires:

strCurrentFile = Dir

I'm sure there has to be a better way of doing what I'm trying to do. The code below needs some other changes I know, but for now I just need something that does the job.

'Initialize variable to iterate through array elements
a = 0
Do While Not rstFileNamePrefixes.EOF
strCurrentFile = Dir(strSourcePath & "*.*")
strCurrentFile = Dir
Do While strCurrentFile <> ""
'Initialize variable for control flow logic.
i = 0
If Left(strCurrentFile, 9) = rstFileNamePrefixes.Fields(0) Then
'Populate the array with the file name.
If Not fs.FileExists(strDestPath & strCurrentFile) Then
arFileFullNames(a) = strCurrentFile
i = i + 1
a = a + 1
End If
End If
If i > 0 Then
'Start going through the recordset again from the beginning.
rstFileNamePrefixes.MoveFirst
'Get out of the current Do Loop so the Dir can pull then next _
file name.
Exit Do
ElseIf Not rstFileNamePrefixes.EOF Then
'Compare the same file name with the next element in recordset
rstFileNamePrefixes.MoveNext
End If
Loop
Loop

 

[vbajock]

Dir() is obsolete, if you are looking for a better way to do it, look into the File Scripting Object:

http://www.virtualsplat.com/tips/visual-basic-fso.asp

Bit of a learning curve, but not bad, and once you master it you will be able to do whatever you want with files and folders. There is also a lot of sample FSO code on the web.

 

[SQLJoe]

Thanks, but is there any way for a file system object to count how many total files are in a folder? I don't see anything in the link you provided that does that, and without the code knowing when to stop, my code will start looping through the folder again from the beginning and bomb when it tries to make another copy with the same name.

?

 

[strongm]

>Dir() is obsolete

According to who? And perhaps you can clarify why the FSO is "a better way to do it" - given, just as one example (and bearing in mind the OP), that the FSO does not support wild cards.

 

[strongm]

Here's a quick rewrite of your code that populates an array with the found files that match the prefix:

[blue]    [green]'Initialize variable to iterate through array elements[/green]
    a = 0
    Do While Not rstFileNamePrefixes.EOF
        strCurrentFile = Dir(strSourcePath & rstFileNamePrefixes.Fields(0) & "*.*")
        Do Until strCurrentFile = ""
            arFileFullNames(a) = strCurrentFile
            a = a + 1
            strCurrentFile = Dir
        Loop
        rstFileNamePrefixes.Movenext
    Loop[/blue]

 

[vbajock]

http://www.google.com/#hl=en&sugexp...t+count+files.count&pbx=1&fp=d63db470d75f1e66