How to Overwrite Existing File When Moving from One Folder to Another

[dgr]

Hello,
I'm using this code to move all my Excel files from 1 folder to another. The code is working file. I need your help to edit the code to overwrite the files in the destination folder if the file names in the source & destination folders are the same. I've tried Application.DisplayAlerts = False but it does not work. Thanks for your help.

Sub test1()
Application.DisplayAlerts = False

    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim FileExt As String
    Dim FNames As String

    FromPath = "E:\path3"
    ToPath = "E:\path5\"

    FileExt = "*.xl*"

    If Right(FromPath, 1) <> "\" Then
        FromPath = FromPath & "\"
    End If

    FNames = Dir(FromPath & FileExt)
    If Len(FNames) = 0 Then
        MsgBox "No files in " & FromPath
        Exit Sub
    End If

    Set FSO = CreateObject("scripting.filesystemobject")
    FSO.MoveFile Source:=FromPath & FileExt, Destination:=ToPath
End Sub

 

[sijpie]

use the MoveFileEx function with an additional argument set to 1
https://msdn.microsoft.com/en-us/library/windows/desktop/aa365240(v=vs.85).aspx

 

[dgr]

Thanks for your reply, sijpie. I have no idea how to incorporate the c++ codes shown in the example as a vba macro. Could you show me the code please? Thanks.

 

[sijpie]

No, don't use the C++ code. It is an example of how to use the function in this .dll with C++. But if the function is in the .dll you can call it with any language you want. Because it is 'undocumented' you need to experiment, but my guess is that the following will work:

FSO.MoveFileEx Source:=FromPath & FileExt, Destination:=ToPath, 1

 

[sijpie]

The best way to learn is to experiment.

How it is with these functions is that they are not part of the VBA. They sit in xxx.dll libraries. In this case the FileSystemObject which you are using is part of scripting, which sits in the sripting runtime library scrrun.dll

if you google on filesystemobject you will find ton's of information plus a lot of VBscript examples, which is similar to VBA.

In the lists of functions I do find MoveFile, but not MoveFileEx. But that MoveFileEx must exist (as it is used by the C++ example shown in the Microsoft site), so as I say, experiment.

I tried it and got an error that the property or method isn't found, in which case we'd need to find out how to get to it.

Doing some more googling I found that the function must be located in kernel32.dll one of windows basic dlls.

Opening the kernel32.dll in WordPad (very useful for this) and searching for 'movefileex' I only found MoveFileExA and MoveFileExW. (Indeed trying to run the function MoveFileEx resulted in a message saying the entrypoint for the function was not found).
In the Microsoft documentation it mentions that MoveFileExA is the ANSI implementation and MoveFileExW is the UNICODE implementation of the function.

however when I code it to use either of these, the code runs without problem, but no files are being moved. So I am a bit stumped.

Declare Function MoveFileExW Lib "kernel32.dll" _
    (ByRef lpExistingFileName As String, ByRef lpNewFileName As String, _
     ByVal dwFlags As Integer) As Boolean


Sub test1()
    
    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim FileExt As String
    Dim FNames As String
    Dim bResult As Boolean


    FromPath = "C:\TestFolder"
    ToPath = "C:\TestFolder2"
'    ToPath = "C:\TestFolder"
'    FromPath = "C:\TestFolder2"


    FileExt = "*.tx*"


    If Right(FromPath, 1) <> "\" Then
        FromPath = FromPath & "\"
    End If


    FNames = Dir(FromPath & FileExt)
    If Len(FNames) = 0 Then
        MsgBox "No files in " & FromPath
        Exit Sub
    End If


'    Set FSO = CreateObject("scripting.filesystemobject")
'    bResult = FSO.MoveFile(FromPath & FileExt, ToPath)
    bResult = MoveFileExW(lpExistingFileName:=FromPath & FileExt, lpNewFileName:=ToPath, dwFlags:=1)
    Debug.Print bResult
End Sub

 

[sijpie]

So the only solution I see is to check for each file if it exists and then delete the oldest of the two in either moveFrom and MoveTo directory