MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Comparing file lists

  Asked By: Darryl    Date: Feb 27    Category: MS Office    Views: 524

This should be simple, but i can't get it working...

I've got a userform on which I can specify two directories,
one 'source' directory and one 'destination' directory. What I'd like
to do is periodically poll the source directory and copy any newly
created files to the destination directory, where I can subsequently
process them.

It seemed to make sense to use arrays to do this, one to hold the
original file list, and one to hold the new file list. I could then
compare the two arrays to figure out which files had been added and
then copy them to the destination directory. Make sense?

I'm using the following code to search the folders and get filenames
as it seemed an easy way to automatically generate an array:

Set RefArray = Application.FileSearch
With RefArray
.LookIn = CStr(lblSrcPath.Caption)
.Filename = CStr(txtFilter.Text)
.Execute SortBy:=msoSortByFileName, _
'results are held in RefArray.FoundFiles(i)
End With

Peridically running the smae code, but changing RefArray for NewArray,
creates the updated filelist.

What I can't figure out is the best way to do the array comparison.
Iterating through both arrays using nested for...nest loops seems
clunky way of doing it as you'd have to iterate the entire way through
RefArray to be sure that the each element of NewArray wasn't present.
And if you get all the way through, then you have to do the file copy
process (i was planning on using Scripting.FileSystemObject). Is there
a more elegant way?



3 Answers Found

Answer #1    Answered By: Komal Mohammad     Answered On: Feb 27

okay... i've kind of made some progress and think this'll work for
the array  comparison, but i have a slightly different problem now...

This is the code  i've come up with:

Private Sub cmdStart_Click()
'check paths exist
If (lblDestPath.Caption = "") Or (lblSrcPath.Caption = "") Or
(txtFilter.Text = "") Then
MsgBox "The Source/Destination folders  or file  Filter don't make
sense!", vbExclamation, "BioFocus DPI : A Galapagos Company"
Exit Sub
End If
'fill initial array
Set RefArray = Application.FileSearch
With RefArray
.LookIn = CStr(lblSrcPath.Caption)
.Filename = CStr(txtFilter.Text)
.Execute SortBy:=msoSortByFileName,
End With
bActive = True
While bActive = True
Interval = CInt(cboInterval.ListIndex)
NowTime = Timer
Do While Timer < (NowTime + (Interval * 60))
'scan source  directory
Set NewArray = Application.FileSearch
With NewArray
.LookIn = CStr(lblSrcPath.Caption)
.Filename = CStr(txtFilter.Text)
.Execute SortBy:=msoSortByFileName,
End With
'compare arrays
For i = 1 To RefArray.FoundFiles.Count
strTemp = Join(RefArray.FoundFiles(i), ",")
Next i
For i = 1 To NewArray.FoundFiles.Count
If StrComp(strTemp, NewArray(i)) Then
'do nothing
'copy file
Set myFSO = CreateObject("Scripting.FileSystemObject")
myFSO.CopyFile NewArray.FoundFiles(i),
CStr(lblDestPath) & " \" & Dir(NewArray.FoundFiles(i))
'do processing bit here....
End If
Next i
'Update RefArray
strTemp = Empty
ReDim RefArray(1 To NewArray.FoundFiles.Count)
For i = 1 To NewArray.FoundFiles.Count
RefArray.FoundFiles(i) = NewArray.FoundFiles(i)
Next i
NewArray = Null
End Sub

I think this should do the periodic scanning for new filenames, and
also do the copying to the second folder. The bit that i can;t get
working is the last 6 lines before the Wend statement. What i'm
trying to accomplish is to replace the original RefArray with the
values in the NewArray so that the next time the directory  is
scanned, it's comparing  to the penultimate file list  rather than the
very first one  generated. I'm sure it's because the RefArray and
NewArray aren't arrays  that i've specifically declared but the result
of the FileSearch object. Are there any elegant ways to get round
this problem?

Answer #2    Answered By: Chau Tran     Answered On: Feb 27

Seeing your lists  are sorted, you just need to work through them item by
item. If the current cell in the new array  is less than the current cell in
the old array, then you have a new file. This means that you need "current
cell" pointers for each array - simple  integers holding the current array

You just need to step these along through the arrays  (if = step both, if <
do something then step one, if > do ?? then step the other). Stop once both
pointers have gone past the end of the arrays.

One complication with array matching is dealing with the last entries in the
arrays (i.e. it might fall off the end of one  array, while the other one
still has entries). You can either do that with more complex IF statements,
or cheat and add an extra item at the end of each array - both of them the
same, and both of them being a very high sort-order, perhaps "~~~~", but
check the ASCII sort order.

What are you going to do about files  that have been deleted from the first
directory? (I.e. my "do ??" above.)

Answer #3    Answered By: Viheke Fischer     Answered On: Feb 27

The technique of using a search  to check in the second array  will work, but
has traps. For instance, you are using a comma join to create a big string
of all the file  names, but not including those commas in your search. Thus
a search for file "X" will match a file name of "Not X".

You should be searching for comma X comma. But, then you need to handle the
cases of the first and last files. To do this, put a comma at the start and
end of the big string

strTemp = "," & Join(RefArray.FoundFiles(i), ",") & ","

However, your statement looks wrong to me anyway. FoundFiles(i) would be
returning only one  file name, and you are overwriting strTemp each time you
go through the loop. I suspect that at the end of the loop, it only has one
file name in it.

You are only doing a one-way check, I think. I.e. you don't look for the
case of a file being deleted from the new array but still being in the ref
array. Don't know whether this is what you want.

You're having trouble with keeping the ref array up to date? At a casual
glance, your code  looks sort of OK, although you are ReDim'ing RefArray
rather than RefArray.FoundFiles. My suspicion is that you don't actually
have local arrays  containing this information. I think you might be using
structures inside the FSO. So your assignment would not be working  or could
be pointing RefArray items into the NewArray structure. Neither would work

I've just plugged your code into VBE. The ReDim of RefArray is being
rejected by a build - "Invalid ReDim". That's because it isn't an array.
Are you getting this error? If so, I wish you had said so.

(On a matter of naming. I don't think that RefArray or NewArray are
actually arrays. The seem to be structures that contain arrays. I think
you'll find that they are of type FileSearch. You should use Option
Explicit and make sure you define all variables to lessen confusion.)

I suggest you grab the directories lists  for both the reference and the new
from FSO each time through the loop. This gets rid of your problem code
completely, and also prevents the directory  getting out of step with this
listing - e.g. if someone deletes a file from the reference directory.

Didn't find what you were looking for? Find more on Comparing file lists Or get search suggestion and latest updates.