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:
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
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