Check file in destination folder if exist

Question

I am trying to determine the number of files that would be copied from the source folder to the destination and then assign this value to the progressbar.max.But using the code below I get Runtime error 5, Invalid procedure call or argument at the marked position.Please Guide

Private Sub cmdCopy_Click()
Dim sFileName As String    'Source File
Dim sDirName As String     'Source Directory
Dim dDirName As String     'Destination Directory
Dim fiFileCount As Integer 'Number of Files to be copied
Dim fbFileMatch As Boolean

If prgFCount.Visible = True Then prgFCount.Visible = False

dDirName = "D:\Destination\"
sDirName = "C:\Source\"
sFileName = Dir(sDirName)


' Disable this button so the user cannot
' start another copy.
cmdCopy.Enabled = False
cmdCancel.Enabled = True
fiFileCount = 0

Do While Len(sFileName) > 0
fbFileMatch = False
If Len(Dir$(dDirName & sFileName)) > 0 Then
fbFileMatch = True
End If
If fbFileMatch = False Then
fiFileCount = fiFileCount + 1
End If

sFileName = Dir '## Error at this Point ##
Loop
If fiFileCount = 0 Then
cmdCopy.Enabled = True
cmdCancel.Enabled = False
Exit Sub
End If
prgFCount.Min = 0
prgFCount.Max = fiFileCount
prgFCount.Visible = True
End Sub

Answer 1

If Len(Dir$(dDirName & sFileName)) > 0 Then

You set up your directory iteration with the line:

sFileName = Dir(sDirName)

Calling the Dir funtion without parameters will get the next item meeting the file name pattern and attributes is retrieved. The Len(Dir$ call is screwing it up.

I would suggest rewriting your code to loop through all the files in your source folder and build a list, then loop through the list and look for matches in your destination folder.

Something like this:

...
sFileName = Dir$(sDirName)
Do While Len(sFileName) > 0
   i = i + 1
   ReDim Preserve strSourceFileList(i)
   strSourceFileList(i) = sFileName
   sFileName = Dir()
Loop

If i > 0 Then
   For i = LBound(strSourceFileList) To UBound(strSourceFileList)
      sFileName = Dir$(dDirName & strSourceFileList(i))
      If Len(sFileName) = 0 Then
         fiFileCount = fiFileCount + 1
      End If
   Next i
End If
...

Answer 2

Dir returns the name of a matching file, directory, or folder. Calling Dir should be fine but in your case it generates the error.

You also have no loop implemented to iterrate through all the available source files.

Using the FileSystemObject is one of the options. To use the FileSystemObject, click the Project menu option, followed by the References... menu option. This will open the References Dialog.

Tick the box beside the reference named "Microsoft Scripting Runtime" and click OK.

Now you can declare a variable as a FileSystemObject. In addition you get access to more objects such as File, Folder, Files and more.

Using the FileSystemObject gives you access to a wide range of features.

The code below demonstrates how to get the count of files which do not exist in the destination and will be copied, using the FileSystemObject.

Private Sub cmdCopy_Click()
    Dim fso As New FileSystemObject
    Dim sourceFolder As Folder
    Dim sourceFile As File
    Dim destinationFolder As Folder
    Dim filesToBeCopied As Integer

    Set sourceFolder = fso.GetFolder("C:\-- Temp --\Source")
    Set destinationFolder = fso.GetFolder("C:\-- Temp --\Destination")

    filesToBeCopied = 0

    ' Iterrate through each file in the source folder.
    For Each sourceFile In sourceFolder.Files
        ' Check if the source file exists in the destination folder
        If Not (fso.FileExists(destinationFolder + "\" + sourceFile.Name)) Then
            filesToBeCopied = filesToBeCopied + 1
        End If
    Next
End Sub

I have tested the above code and it correctly increments the count of filesToBeCopied to the expected number.