Save file with specific name

[Dr. Schwartz]

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]

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]

Thank you Dave it works nicely, however I was not good explaing that I always
need the next (high) number. So in your example below A0018 should be ignored
and it should be named A0025.xls instead. Can you add this?

Thanks a lot
The doctor

 

[Dave Peterson]

How about:

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"
myPath = "C:\my documents\excel"
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 = 9999 To 1 Step -1
myFileName = myPath & "A" & Format(iCtr, "0000") & ".xls"

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

If TestStr = "" Then
'keep looking
Else
If iCtr = 9999 Then
MsgBox "No available numbers!"
Exit For
Else
FoundOne = True
myFileName = myPath & "A" & Format(iCtr + 1, "0000") & ".xls"
Exit For
End If
End If

Next iCtr

If FoundOne = False Then
myFileName = myPath & "A0001.xls"
End If

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 Sub

 

[Dr. Schwartz]

Good idea - unfortunatly I'm to impatient to wait for the loop to run ~9999
times.

Is this the only way to solve this by looping through the file names?

 

[Dave Peterson]

How long did it take to do that loop?

I guess you could loop through the directory and look for names that match
A####.xls. Then find the maximum number used and add one to it.

Good idea - unfortunatly I'm to impatient to wait for the loop to run ~9999
times.

Is this the only way to solve this by looping through the file names?