Checking if a file is already saved in a different folder

Question

Dears I have this piece of code that checks if a file .xls in a target folder is already saved under format .xlsb in the ActiveWorkbook folder. this works properly for the first file but the loop stops after that and doesn't checks the remaining ones.

myFile = Dir(myPath & myExtension)

'check if the file .xls is in the current folder in format .xlsb
Do While myFile <> ""
    If Dir(Application.ActiveWorkbook.Path & "\" & Replace(myFile, ".xls", ".xlsb")) <> "" Then
        Debug.Print myFile & " is in the folder"
    Else
        Debug.Print myFile & " is not in the folder"
    End If

'next file
myFile = Dir
Loop

Create an array of filenames first, **then** use your `check_existence` function on each of the elements of the array. (You can't perform one `Dir` within another `Dir` - only the latest one's file specification is kept for use in the `myFile = Dir` statement which gets the next match.) Otherwise, use the File Scripting Object. — YowE3K, Jan 24 '18 at 09:31

score 0 · Answer 1 · answered Jan 24 '18 at 09:57

You haven't created an array for looping the files from. Below is the code for checking file existance

Sub checkExistance()
    'setup
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder("Your Folder Path Here")

    'file
    Dim myFile As String
    Dim FileName As String
    Dim FileExtension As String
    FileName = "Your File Name"
    FileExtension = ".xls"
    myFile = FileName & FileExtension

    'Loop through each file in folder
    For Each objFile In objFolder.Files
        If objFile.Name = Replace(myFile, ".xls", ".xlsb") Then
            MsgBox objFile.Name & " Ci sta"
        Else
            MsgBox objFile.Name & " Nun Ci sta"
        End If
    Next
End Sub

score 0 · Answer 2 · answered Jan 24 '18 at 10:15

There is a function on another answer HERE that returns an array of files within a folder. If you grab that, you can get what you need with:

Dim myFile As Variant
Dim folder_containing_xls As String
Dim folder_containing_xlsb As String

folder_containing_xls = "FOLDER PATH HERE"
folder_containing_xlsb = Application.ActiveWorkbook.Path 'or "OTHER OR SAME FOLDER PATH HERE"

If Right(folder_containing_xls, 1) <> "\" Then folder_containing_xls = folder_containing_xls & "\"
If Right(folder_containing_xlsb, 1) <> "\" Then folder_containing_xlsb = folder_containing_xlsb & "\"

For Each myFile In listfiles(folder_containing_xls)
    If myFile Like "*.xls" Then
        If Dir(folder_containing_xlsb & Replace(myFile, ".xls", ".xlsb")) <> "" Then
            Debug.Print myFile & " is in the folder"
        Else
            Debug.Print myFile & " is not in the folder"
        End If
    End If
Next

I couldn't work out if you were looking for both files to be in the same folder, or if they were in different folders, so I've built it to cope with either.

Checking if a file is already saved in a different folder

2 Answers2