saving file names in VB

[kristabelle]

I am trying to write VB to allow me to Save As file names that are numerical in order (ex. name2630, name2631, name2632, etc.). Is there an easier way to do this than to manually change the file name each time? Any help is much appreciated.

[Mark O'Brien]

using your example, you could set up a loop to create the filename, e.g.:

Dim as integer

dim sName as String

Dim sFirstPart as String





sFirstPart = "Name"

  for i = 2630 to 2631

    sName = sFirstPart & i

    'put your save as code here using sName as the filename.

  next

HTH

[kristabelle]

That'll do it. Many thanks!
-KB

[Tom Schreiner]

Hi
Is this all done at once or is it going to be an ongoing tally?

I'll assume it is ongoing unless you say otherwise...

This will search the named folder and increment the value of the highest filename(value) found by one and then save the active workbook to the same named path with the new incremented values/filename...

Tongue-twister!

Anyway, try it out.

Sub SaveIncrementNumFileName_xls()
Dim SaveAsThisName As String
Dim FolderToSearch As String
Dim i As Integer
Dim FoundFileNameValue(2) As Long

With Application.FileSearch
FolderToSearch = "C:TestNum" 'edit this to your path
.LookIn = FolderToSearch
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
SaveAsThisName = .FoundFiles(i)
FoundFileNameValue(1) = Val(Mid(SaveAsThisName, Len(FolderToSearch) + 2, _
Len(SaveAsThisName) - Len(FolderToSearch) + 1))
If FoundFileNameValue(1) > FoundFileNameValue(2) Then _
FoundFileNameValue(2) = FoundFileNameValue(1)
Next
End If
End With
FoundFileNameValue(2) = FoundFileNameValue(2) + 1
ActiveWorkbook.SaveAs FolderToSearch & "/" & FoundFileNameValue(2) & ".xls"
End Sub

Oh well, post already answered.
Something new for me anyway.
Tom



[ This Message was edited by: TsTom on 2002-04-15 21:32 ]

[Damon Ostrander]

Hi Kristabelle,

Here is a macro that when you run it will add one to the file name of the active workbook and do a SaveAs. To install it, use Alt-TMV and paste the code into the macro module code pane that appears. To make its use easy you can assign this macro to a button or, using the instructions commented in the code, make it the workbook's default Save mode.

Sub IncrementAndSaveAs()

'This macro saves the active workbook, which is assumed to have
'a filename composed of a name and a number, such as "Inventory060.xls".
'This macro increments the filename to Inventory061.xls, and does a
'SaveAs to this filename. To make this macro execute automatically
'on each save, use the Workbook_Save event macro with the code:

'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' If Not SaveAsUI then
' SaveAsUI = False
' Cancel = False
' IncrementAndSaveAs
' EndIf
'End Sub

'Note: The number of numeric digits you use in the file name determines
' how many saves can be done before the number wraps to zero. For
' example, after Inventory999.xls would be Inventory000.xls, so three
' digits allows for 999 saves before wrapping to zero.

'find out how many digits in current filename

Dim CurrentName As String
Dim NewName As String
Dim nLen As Integer
Dim NewNumber As String

CurrentName = ActiveWorkbook.Name
If Right(CurrentName, 4) = ".xls" Then
'strip off .xls
CurrentName = Left(CurrentName, Len(CurrentName) - 4)
End If

For nLen = 6 To 1 Step -1
NewNumber = Right(CurrentName, nLen)
If IsNumeric(NewNumber) Then
GoTo SetNewName
End If
Next nLen
MsgBox "File name does not end with numeric string" & vbCrLf & _
"New numeric version cannot be created.", _
vbCritical, "Save Not Done"
Exit Sub

SetNewName:
NewName = Left(CurrentName, Len(CurrentName) - nLen) & _
Right(CStr(CLng(NewNumber) + 1), nLen)

ActiveWorkbook.SaveAs Filename:=NewName

End Sub