ExcelBanter - Save file with specific name

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Save file with specific name (https://www.excelbanter.com/excel-discussion-misc-queries/174239-save-file-specific-name.html)

Save file with specific name

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

Save file with specific name

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

Save file with specific name

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" wrote:

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

Save file with specific name

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 wrote:

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" wrote:

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

--

Dave Peterson

Save file with specific name

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" wrote:

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 wrote:

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" wrote:

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

--

Dave Peterson

Save file with specific name

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.

Dr. Schwartz wrote:

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" wrote:

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 wrote:

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" wrote:

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

--

Dave Peterson

--

Dave Peterson