This is a portion of a macro that checks for an existing filename, then just uses a +1 formula to select the next number in 3 digits, IF it's already been used.
i.e. ...345.csv to ...346.csv.
I just never change the number to anything other than the NEXT number as above, so I'd like to SKIP the MsgBox and I guess just default to OK without clicking on it.
The only worry is whether it would move another +1 number IF (in the example above) 346.csv also existed, automatically moving to ...347.csv.
Code
' Check Existing file & Saving
For j = 1 To 10
If Dir(pth & "\" & fn1 & ".csv") = "" Then
wk1.Delete
wb.SaveAs Filename:=pth & "\" & fn1 & ".csv", FileFormat:=xlCSV ', ConflictResolution:=xlLocalSessionChanges ' currentBook.Close SaveChanges:=False
wb.Close
Exit Sub
Else
fn2 = Format(Date, "dddmmmdd") & "-" & CLng(Mid(wb.Worksheets(1).Cells(i, 8), 1, 3)) + j
If Dir(pth & "\" & fn2 & ".csv") = "" Then
[COLOR=#ff0000][B] rply = InputBox("File " & pth & "\" & fn1 & ".csv" & vbCrLf & "already exist, Would you like to change this file" & vbCrLf & "with following available name or change name as you like", "CSV File Name", pth & "\" & fn2 & ".csv", vbOKCancel)
If rply = "" Then
MsgBox "File name is not valid"[/B][/COLOR]
wk1.Delete
wb.Close
Exit Sub
Else
fn1 = fn2
End If
End If
End If
Next j
wb.Close
End Sub
Display More
Thank you for this little twist!