rename files

[Bas Cost Budde]

Hi bas,

Thnx for the effort, i could be finished by now . But that's not the
point. I still want to learn how it can be done. Tim also replied to this
subject and has an example code. Could you take a look at it?

Thanks Tim.

public sub GetSubDirs(strRoot)
do while len(dir())>0
AddItToArray
loop

for each strDirName in MyArray
GetSubDirs strDirName
next strDirName

End Sub

Let's see.

This routine calls itself, after having finished with the current
directory, for every subdirectory. An initial dir("pathname",
vbdirectory) is assumed. AddItToArray must be written, and should use
the MyArray mentioned furtheron.

I wish to announce I got plain files inbetween my directories. Using the
vbDirectory constant!

Now I'm getting stuck on termination of the algorithm. In the above
fragment, For Each is used, and inside that loop the size of the array
can (will likely) grow. I never tested what will be looped through then.
And what will the second level recursion try to do? Scan the directories
again?

I'm sorry to say I cannot provide a complete answer for this. I don't
have such a setup to run full-scale tests, and I don't want to spend
time to build a test environment, so to say, there are projects I have
to finish too.

I will keep it in mind.

 

[Tim Ferguson]

I wish to announce I got plain files inbetween my directories.

Yes, me too. There is a line in the small print in the help file for the
Dir function vis:

Calling Dir with the vbDirectory attribute does not continually return
subdirectories.

.... which kind of screws it up for anything useful! I wasn't planning to
work out a full solution, but this seems to work. You'll need a reference
set to the Microsoft Scripting Runtime.

Public Sub IterateTree(Root As String)

' can't use Dir() as it won't return successive folders
' in a wildcard search
'
' The FSO needs a reference to MS Scripting Runtime
Dim fso As FileSystemObject
Dim fld As Folder
Dim fldSub As Folder

' set up a local FSO
Set fso = New FileSystemObject

' just check we are not on a wild goose chase
If Not fso.FolderExists(Root) Then
' you could also do a Err.Raise here if
' you preferred
MsgBox "No such folder as " & Root
' not modular to have two exits, but it's only
' a demonstration!
Exit Sub

End If

' set a pointer to the current folder
Set fld = fso.GetFolder(Root)

' do something useful with the current directory
' put this line after the For Each fldSub section
' if you want post-ordering iteration rather than
' pre-order
Debug.Print fld.Path

' recurse through each of the subfolders
For Each fldSub In fld.SubFolders
' recursive call, limit is set implicitly
' because if subfolders.count=0 it just
' won't go any further
IterateTree fldSub.Path

' that's as easy as it gets!
Next fldSub

End Sub



Hope that helps

Tim F

 

[Marshall Barton]

I still want to learn how it can be done.

Here the key part of the procedure I used for this kind of
thing:

Public Sub ListFiles(path As String)
Dim SubDirs(255) As String
Dim strFile As String
Dim K As Integer, J As Integer

On Error GoTo ErrHandler

Debug.Print path
' Build list of subdirectories
strFile = Dir(path & "\", vbDirectory)
Do Until strFile = ""
If GetAttr(path & "\" & strFile) = vbDirectory _
And strFile <> "." And strFile <> ".." Then
SubDirs(K) = path & "\" & strFile
K = K + 1
End If
strFile = Dir
Loop

' Process each workbook
strFile = Dir(path & "\*.XLS")
Do Until strFile = ""
Debug.Print , strFile
strFile = Dir
Loop

' Repeat all this for each subdirectory
For J = 0 To K - 1
ListFiles SubDirs(J)
Next J

ExitHere:
Exit Sub

 

[Jason]

Hi Tim,

Thanks for the example. That is exactly what i want.

Another question, how can i use results in a table in access? I would also
like to insert the filesnames, size and dates into an table.

Greetings,

J

 

[Tim Ferguson]

Another question, how can i use results in a table in access? I would
also like to insert the filesnames, size and dates into an table.

I see this question all the time, and I never understood why people would
want to do this. Anyway, it's pretty easy, as you can read FileSize and
various dates using plain VBA or the FSO. If you are reading ordinary
files, then there is no problem using the Dir() function -- it's only the
vbDirectory argument that fails to go round and round.

It's one of those times when it's probably just as neat to use a ADO-type
..AddNew and .Update loop; but you could also easily knock up the
appropriate "INSERT INTO Files (FName, Length, UpdateDate) VALUES (...)"
type of command.

Hope that helps


Tim F

 

[Jason]

Hi Tim,

Could you provide me such code? I'm want to learn from it. My skills are not
as good as yours.

I would like to add the filenames with their size and dates based on their
extensions. This needs to be done like your example before to iterate
directories.

Thnx J

 

[Tim Ferguson]

I would like to add the filenames with their size and dates based on
their extensions. This needs to be done like your example before to
iterate directories.

I did not really want to spend much time on this, but here is some air code
which has _not_ been tested... Firstly, you need a hook from the tree
crawling code last time:

' do something useful with the current directory
' put this line after the For Each fldSub section
' if you want post-ordering iteration rather than
' pre-order
Debug.Print fld.Path

and change the line to something like -- note that you should already have
opened a recordset to store the file details

SaveFolderContents fld.Path, rstRecordsetOnTheFilesTable

and the sub itself would look like

Public Sub SaveFolderContents(SomePath as String, _
SomeRecordset As DAO.Recordset)

Dim fso as FileSystemObject ' yes, I am that lazy!
Dim fil as File ' get the OS to do all the work
Dim fld as Folder

Set fso = New FileSystemObject
Set fld = fso.GetFolder(SomePath)

For Each fil in fld.Files
' screen out files you don't want
If fil.Type <> "tmp" Then
' use the recordset you were given
With SomeRecordset
.AddNew ' new empty record
!FilePath = fil.Path ' fill up the fields
!FileName = fil.Name
!FileSize = fil.Size
!FileDate = fil.DateLastModified
.Update ' save the new record
End With
End If
Next fil

' don't close the recordset because the calling code will
' expect it back in the same state is was in before

End Sub


This should probably work but treat it with great care...

B Wishes


Tim F