[SOLVED] Rename and Move File

mar050703 · Oct 30th 2017

Hi Experts

I think I am almost there...

I am looping through files in a folder, opening them, doing stuff in them, and then closing the file. After the close is where I have the problems

I am wanting to move that file to a new location, but... if the file exists in the new location, rename it as file(1) (or 2 or 3 - basically increment the number by 1.

The code I have is:

Code

Sub Move()


Dim MyPath As String
Dim DestPath As String
Dim MyFile As String
Dim MyExt As String
Dim wb As Workbook
Dim NewFile As String
Dim fso As Object




MyPath = "C:\Users\Owner\Desktop\Test\"
DestPath = "C:\Users\Owner\Desktop\Test\Error\" 'Destination to move completed file too
MyExt = "*.xlsx"
MyFile = Dir(MyPath & MyExt)
NewFile = "ERROR - " & MyFile
Set fso = CreateObject("Scripting.filesystemobject")




'Open Source File if like Cash Management
Do Until MyFile = ""
    If MyFile Like "*ERROR*" Then
        Set wb = Workbooks.Open(Filename:=MyPath & MyFile)
' ........do lots of stuff
        wb.Close
        If Not fso.fileexists(DestPath & MyFile) Then
            MsgBox "File does not exist", vbOKOnly
            fso.MoveFile Source:=MyPath & MyFile, Destination:=DestPath
        Else
            MsgBox "File Exists", vbOKOnly
            Dim i As Long
            Dim ReFile As String
            ReFile = MyFile
            Do Until DestPath & ReFile = ""


                ReFile = Replace(MyFile, Mid(MyExt, 2), "(" & i & ")" & Mid(MyExt, 2))
                i = i + 1
                Loop
                fso.MoveFile Source:=MyPath & MyFile, Destination:=DestPath & ReFile      


        End If  


    End If
    If MyFile Like "*Cash Management*" Then
       GoTo NextFile




    End If


NextFile:
MyFile = Dir
Loop
End Sub

Display More

My problem is that the loop code to increment just keeps looping (I can see the number increases everytime), I need it to get to the next available number and move the file.

Any help would be appreciated

mar050703 · Oct 30th 2017

Ok, partial solve - I have moved the loop, giving me

Code

Loop
fso.MoveFile Source:=MyPath & MyFile, Destination:=DestPath & ReFile

but now I have notices 2 things
1) The file is called Filename(0) not Filename (1)
2) When the file is moved, I get a file not found error - in the loop

Thanks for any help

mar050703 · Oct 30th 2017

Hi All,

I have solved the issue.

I needed to do a simple Do.. Until

I had a minor issue of the Number not resetting, for the second file, so I needed to reset that to 0

The final code I used was:

Code

If Not fso.FileExists(DestPath & MyFile) Then
            fso.MoveFile Source:=MyPath & MyFile, Destination:=DestPath
        Else
            Dim i As Long
            Dim ReFile As String


            Do
                i = i + 1
                ReFile = Replace(MyFile, Mid(MyExt, 2), "(" & i & ")" & Mid(MyExt, 2))
            Loop Until Not fso.FileExists(DestPath & ReFile)
                fso.MoveFile Source:=MyPath & MyFile, Destination:=DestPath & ReFile
                i = 0


        End If

Display More

[SOLVED] Rename and Move File

Participate now!

Share