VBA Loop until not error

[cr731]

I am trying to save a new version of a file and have the following line to delete an existing version if there is one:

If Len(Dir(New_File_Name)) Then Kill New_File_Name

However... if somehow the file does exist and the file is currently open, the line above fails.

I'd like to create a loop that will check if the above is an error. If it is an error, add "2" to the end of the file name, and retry that. Once there is no error, then carry on with saving the new file.

I'm really bad with For / Until looping... is this easy?

 

[AlphaFrog]

I am trying to save a new version of a file and have the following line to delete an existing version if there is one:

If Len(Dir(New_File_Name)) Then Kill New_File_Name

However... if somehow the file does exist and the file is currently open, the line above fails.

I'd like to create a loop that will check if the above is an error. If it is an error, add "2" to the end of the file name, and retry that. Once there is no error, then carry on with saving the new file.

I'm really bad with For / Until looping... is this easy?

This is pretty easy.

Does your file name already include the file extension e.g; .xlsx? If yes, what is the extension? Can you show the code where the file name is generated? This all matters in how the file number is added to the name.

It would be a little easier if the file path was one variable, the base file name was another variable and the file extension was a third variable.

Example:

    [COLOR=darkblue]Dim[/COLOR] strFileName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] strPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] strFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] strExt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    strPath = "C:\Test\"
    strFile = "MyFile"
    strExt = ".xls"
    i = 1
    
    strFileName = strPath & strFile & strExt
    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] Len(Dir(strFileName)) = 0
        i = i + 1
        strFileName = strPath & strFile & "(" & i & ")" & strExt
    [COLOR=darkblue]Loop[/COLOR]
        
    MsgBox strFileName

 

[cr731]

What if the first go is no error and I don't need to add a number at all?

also, should it be until <>0?

 

[AlphaFrog]

What if the first go is no error and I don't need to add a number at all?

also, should it be until <>0?

The Do-Loop doesn't run if you don't need to add a number.

It should be = 0 to loop until the test file name doesn't exist (a new file name).

You're welcome.

 

[cr731]

The Do-Loop doesn't run if you don't need to add a number.

It should be = 0 to loop until the test file name doesn't exist (a new file name).

You're welcome.

Thanks, that makes sense now.

Sorry though, I did a poor job of explaining what I need.

I do want to overwrite the existing file, so I don't want to loop until the file doesn't exist. The only issue is if the file I'm trying to overwrite is already open, the obviously you cannot overwrite an open file. So I need something that will,

1. If file is not open, overwrite existing.
2. If file is open, add -1 to the end of the file name and redo this action until no error exists (i.e. if by some rare chance the file with -1 at the end is also open, move to -2 and try again).

 

[AlphaFrog]

[color=darkblue]Sub[/color] Macro1()
    [color=darkblue]Dim[/color] strFileName [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strExt [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    strPath = "C:\Test\"
    strFile = "MyFile"
    strExt = ".xls"
    
    strFileName = strPath & strFile & strExt
    [color=darkblue]Do[/color] [color=darkblue]Until[/color] [color=darkblue]Not[/color] FileInUse(strFileName) [color=green]'Test if file is in use[/color]
        i = i - 1
        strFileName = strPath & strFile & "(" & i & ")" & strExt
    [color=darkblue]Loop[/color]
    [color=darkblue]If[/color] Len(Dir(strFileName)) [color=darkblue]Then[/color] Kill strFileName
    
    MsgBox strFileName, , "Save file useing this file name"
    [color=green]'ActiveWorkbook.SaveAs Filename:=strFileName[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
    
[color=darkblue]Public[/color] [color=darkblue]Function[/color] FileInUse(sFileName) [color=darkblue]As[/color] [color=darkblue]Boolean[/color]
[color=green]'http://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open-using-vba[/color]
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    [color=darkblue]Open[/color] sFileName [color=darkblue]For[/color] [color=darkblue]Binary[/color] [color=darkblue]Access[/color] [color=darkblue]Read[/color] [color=darkblue]Lock[/color] [color=darkblue]Read[/color] [color=darkblue]As[/color] #1
    [color=darkblue]Close[/color] #1
    FileInUse = IIf(Err.Number > 0, [color=darkblue]True[/color], [color=darkblue]False[/color])
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
[color=darkblue]End[/color] [color=darkblue]Function[/color]

 

[cr731]

Thanks!