Checking directory to save a file

Posted by Kevin on January 27, 2001 10:28 AM

I'm saving a file by cell reference. Is it possible to check the directory into which I'm saving the file for duplicate names, then change the name of the file to be saved by adding a 1, 2, 3, ... ?

Posted by Celia on January 27, 2001 8:28 PM

Kevin
Check whether this works :-

Sub Save_Workbook()
Dim NewWB$, x%, y%
NewWB = Range("A1").Value
x = 0
Do
If Dir(NewWB & ".xls") = "" Then
ActiveWorkbook.SaveAs Filename:=NewWB
Exit Do
Else
x = x + 1
If y = 0 Then NewWB = NewWB & " - "
NewWB = Left(NewWB, InStr(NewWB, " - ") + 2) & x
End If
Loop
End Sub

Celia

Posted by Celia on January 28, 2001 1:28 AM

Correction

Sub Save_Workbook()
Dim NewWB$, x%
NewWB = Range("A1").Value
Do
If Dir(NewWB & ".xls") = "" Then
ActiveWorkbook.SaveAs Filename:=NewWB
Exit Do
Else
x = x + 1
NewWB = NewWB & " - "
NewWB = Left(NewWB, InStr(NewWB, " - ") + 2) & x
End If
Loop
End Sub

Posted by Kevin B. on January 29, 2001 9:12 AM

Re: Correction

Thank you. I'll try it out.