Checking if any files in folder are in another

**Directlinq** · 11-06-2009, 03:16 PM

Hi how can i look at all the .mpg files in a folder and check if any of them exist in another folder.

ive got this code but i cant get it to work properly.

If anybody could help

Code:

MsgBox "checking files that already exits"
Dim MyFile As String, Sep As String
Dim oFSO, sFileName
Dim sDoc As String
Dim sPath As String
MyFile = Dir("D:\" & "*.mpg")
 
      Do While MyFile <> ""
 
sPath = "F:\MediaLib\Video\"
If (Dir(sPath & MyFile) > "") Then
MsgBox "Videos on this disc have already been uploaded to this system"
Exit Sub
End If
         
         MyFile = Dir()

      Loop
 
MsgBox "continue"
Exit Sub

Many Thanks

**mrice** · 11-06-2009, 03:57 PM

I suspect that the problem is that you are using the DIR function twice and the second is resetting it so that the DIR() ar the end of the first loop is not looking back into the first folder.

Why not drop the filenames of the found files in the two folders into separate columns in a sheet and compare there?

**Directlinq** · 11-06-2009, 04:12 PM

that is an option but i was hoping i could do this purely in vba. have you got any ideas how or if it can be done.

Many thanks

**mrice** · 11-06-2009, 04:32 PM

How about

Code:

Sub Test()

Dim Folder1Array()

ReDim Folder1Array(0)

MsgBox "checking files that already exits"
Dim MyFile As String, Sep As String
Dim oFSO, sFileName
Dim sDoc As String
Dim sPath As String
Dim Uploaded As Boolean

MyFile = Dir("D:\" & "*.mpg")
Do While MyFile <> ""
    ReDim Preserve Folder1Array(UBound(Folder1Array) + 1)
    Folder1Array(UBound(Folder1Array)) = MyFile
    MyFile = Dir()
Loop


sPath = "F:\MediaLib\Video\"
MyFile = Dir(sPath & "*.mpg")
Uploaded = False
Do While MyFile <> ""
    For N = 1 To UBound(Folder1Array)
        If Folder1Array(N) = MyFile Then
            Uploaded = True
            Exit Do
        End If
    Next N
Loop
If Uploaded = True Then
MsgBox "Videos on this disc have already been uploaded to this system"
End If
End Sub

**Directlinq** · 11-06-2009, 07:00 PM

Hi im using your code above. Which i thank you emensly for and it works if there is a file on the F:\MediaLib\Video\ named the same as on the d:\, but if there is a file in F:\MediaLib\Video\ that is not on the d:\. The whole application stops responding.

Any ideas?

Many Thanks for your help

**Directlinq** · 11-07-2009, 04:16 AM

Can nobody help me?.
Please any advise would be much appreciated.

Many Thanks

**mrice** · 11-07-2009, 09:11 AM

My fault - missed a line out

Code:

....

Do While MyFile <> ""
    For N = 1 To UBound(Folder1Array)
        If Folder1Array(N) = MyFile Then
            Uploaded = True
            Exit Do
        End If
    Next N
    MyFile = Dir() 'Add this line in order to force loop to look at next file
Loop

......