VBA to move files (duplicated filename handling)

[Dampa88]

Dears,

I have a macro that is opening all txt file from a specified folder, do some actions in Excel and then move them to a destination folder.

I'd like to avoid possible errors in case the file already exists in the destination folder.
To move the files I use File.Move which I guess has no feature to handle this situation.

I though to rename the file in the loop adding the timestamp at the end, but my VBA knowledge is still quite limited.

Sub Test()

    Dim orig_path As String, path As String, MyFile As Variant, a As Variant, count As Integer

    orig_path = "C:\AAA"
    dest_path = "C:\BBB"
    
    With CreateObject("scripting.filesystemobject")
     For Each MyFile In .GetFolder(orig_path).Files
       If .GetExtensionName(MyFile) = "txt" Then

        'long code here to do open txt file, import in excel, etc.

        MyFile.Move (dest_path & "\" & MyFile.Name)
        End If
      Next
    End With
   
End Sub

Do you have some advices for me?

Thanks,
D

 

[rlv01]

I'm not sure I understand what you are asking. Are you having some problem with the code you posted? If so, can you explain further?

 

[Dampa88]

Hi rlv01, yes I'm having some problems with the code.

For example, let's assume that I have two files called Test1.txt and Test2.txt in the folder C:\AAA

The first run of the macro will be fine, the txt files will be elaborated and then moved to C:\BBB
For the future run, if I will have again a file named Test1.txt in the folder C:\AAA, I will get an error when it tries to move it to the destination folder, as a same file already exists.

For this reason, before moving the file I was thinking to put a timestamp, so that in the destination folder I'll have:
Test1 20230522130105.txt
Test2 20230522130105.txt
Test1 20230523125107.txt
Etc.

I hope it's clearer now, thanks!

 

[rlv01]

For this reason, before moving the file I was thinking to put a timestamp

Or you could overwrite any existing file with the new file. Is there a reason you don't want to do that?

 

[Dampa88]

Yes, I need to keep history of the files, duplicated ones included.

 

[rlv01]

Not tested.

Sub Test()

    Dim orig_path As String, path As String, a As Variant, count As Integer
    Dim dest_path As String, NewName As String
    Dim MyFile As Object

    orig_path = "C:\AAA"
    dest_path = "C:\BBB"

    With CreateObject("scripting.filesystemobject")
        For Each MyFile In .GetFolder(orig_path).Files
            If .GetExtensionName(MyFile) = "txt" Then

                'long code here to do open txt file, import in excel, etc.

                If .FolderExists(dest_path) Then
                    NewName = dest_path & "\" & .GetBaseName(MyFile.Name) & Format(Date, "yyyymmdd") & Format(Time, "hhmmss") & ".txt"
                    .CopyFile MyFile.path, NewName, True
                End If
            End If
        Next
    End With
End Sub

 

[Dampa88]

Tested and works perfectly! Thanks a lot!
I've just added a .DeleteFile MyFile.path so that files are deleted from older folder.