If you have A0001.xls through A0024.xls and someone deletes a0018.xls, then this
will use a0018.xls--the first available number:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim FoundOne As Boolean
Dim TestStr As String
Dim myFileName As String
Dim myPath As String

myPath = "C:\datafolder"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

TestStr = ""
On Error Resume Next
TestStr = Dir(myPath & "nul")
On Error GoTo 0

If TestStr = "" Then
MsgBox myPath & " is not a valid folder!"
Exit Sub
End If

FoundOne = False
For iCtr = 1 To 9999
myFileName = myPath & "A" & Format(iCtr, "0000") & ".xls"

TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
FoundOne = True
Exit For
Else
'keep looking
End If

Next iCtr

If FoundOne = False Then
MsgBox "No available files!"
Else
On Error Resume Next
ThisWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal
If Err.Number < 0 Then
MsgBox "Error Save failed!"
Err.Clear
Else
MsgBox "File saved as: " & myFileName
End If
On Error GoTo 0
End If

End Sub

Dr. Schwartz wrote:

I have a template workbook (Excel 2003) that the user enters data in. When
finished the user should press a button and the script should look in a
specific folder:

c:\datafolder

The folder contains a list of files:

A0001.xls
A0002.xls
A0003.xls

The script should now save the file with the next "available" file name:
A0004.xls and enter the file name in cell A1.

Can anyone help me out?
The Doctor

--

Dave Peterson