updating file into one folder's subfolders vba

Question

I would like to update a file in current subfolders with excel VBA. First step is looking for a file name in subfolders. List them all in another sheet so I can keep log for that. Copy and overwrite the file with new file, so all my folders and subfolders will be updated with new file.

source
D:\home
destination
D:\dest\cus1\...

I am currently using below code, but I need to improve at least for loop or any new algorithm. Can you please help?

Sub sbCopyingAllExcelFiles()

    Dim FSO
    Dim sFolder As String
    Dim dFolder As String

    sFolder = "c:\Users\osmanerc\Desktop\STATUS\" ' change to match the source folder path
    dFolder = "\\manfile\ELEKTRONIK\MUSTERI DESTEK\ECN management\" ' change to match the destination folder path
    Set FSO = CreateObject("Scripting.FileSystemObject")

    If Not FSO.FolderExists(sFolder) Then
        MsgBox "Source Folder Not Found", vbInformation, "Source Not Found!"
    ElseIf Not FSO.FolderExists(dFolder) Then
        MsgBox "Destination Folder Not Found", vbInformation, "Destination Not Found!"
    Else
        FSO.CopyFile (sFolder & "\*.xl*"), dFolder
        MsgBox "Successfully Copied All Excel Files to Destination", vbInformation, "Done!"
    End If
End Sub

There is already a built in function to copy files as you do above: [reference here](https://stackoverflow.com/a/16943127/2727437). If you want to copy lots of files from the same folder to a single destination folder you could look into [iterating over all files in a folder like this](https://stackoverflow.com/a/43369428/2727437) — Marcucciboy2, Jun 04 '18 at 17:41
but the point is I want to update same file in different folders in directory. ıt is not about collecting files in one folder. — budabej, Jun 04 '18 at 19:01

Marcucciboy2 · Answer 1 · 2019-03-04T18:35:03.827

So this should be able to copy all of the files from your source that match the Like sFolder & "\*.xl*" pattern. You can add more calls if you have more folders to work with.

Sub sbCopyingAllExcelFiles()

    Call SafeCopy("c:\Users\osmanerc\Desktop\STATUS\", "\\manfile\ELEKTRONIK\MUSTERI DESTEK\ECN management\")
    'Call SafeCopy("another source folder", "another destination folder")
    'Add more function calls as necessary

End Sub

Function SafeCopy(ByVal sFolder As String, ByVal dFolder As String)

    Dim count As Integer

    Dim FSO As Object
    Dim Folder As Object
    Dim File As Object

    Set FSO = CreateObject("Scripting.FileSystemObject")

    If Not FSO.FolderExists(sFolder) Then
        MsgBox "Source Folder Not Found: " & vbCrLf & sFolder, vbInformation, "Source Not Found!"
        Exit Function
    ElseIf Not FSO.FolderExists(dFolder) Then
        MsgBox "Destination Folder Not Found: " & vbCrLf & dFolder, vbInformation, "Destination Not Found!"
        Exit Function
    Else
        Set Folder = FSO.GetFolder(sFolder)

        For Each File In Folder.Files
            If File.Name Like sFolder & "\*.xl*" Then
                FSO.CopyFile File.path, dFolder
                count = count + 1
            End If
        Next

        MsgBox "Copied " & count & "files to destination", vbInformation, "Copy Successful"
    End If

End Function

it is not copying any files. something is missing. it says zero file is copied — budabej, Feb 26 '19 at 19:32
@budabej how about now? Think I was just missing the `sFolder` variable in the `Like` statement — Marcucciboy2, Mar 04 '19 at 18:35

updating file into one folder's subfolders vba

1 Answers1