Auto file naming

[rong_peng]

Atthe open, or save function I want to save the file automatically, the file name to be based off of existingfiles. So for example I have 10 files ina folder named 1234-01K, 1234-02K, 1234-03K... I want my new file name to bethe next in order. This in either MSWord or Excel.
Any ideas?


Thanks

[Frosty]

Lots of ideas. Lots of questions.

Is it only the 01 that gets incremented?
Does the 1234 increment as well when 01 hits 99?
Does the K get incremented up to L at some point?
How do you know what folder to check for the files?
Are you opening an existing document or a new document?
Are you REALLY sure you want to increment up (in whatever fashion) every time you actually save the file? Because CTRL+S is pretty easy, and I'm not sure you want to, essentially, increment a new version of the document every single time you Save. You will rapidly have 1000s of files if you have a "good" user who regularly saves a document while working on it.

[rong_peng]

Yes only the 01, the rest will stay the same. I already have code to save to a folder. I'd like to have a template that I'd work from, have the file save as and then keep that file name when I press save.
An old company I worked for once had a script that ran in AutoCAD and what it did was the following; I would open a template and run the new name code, the file would save as the new name and that was it. If I pressed save the name would remain the same. The name would be one number greater than the files already in the folder. So once I was done with that file, I would run that code again and it would create a new file with the number increasing by 1, seems simple to just press saveas, however if you have 15 people all creating new files in the same folder nobody knows what to call their file.

Hope that makes sense.

[Frosty]

Try something along these lines... pass in the activedocument (will need some modifications for ActiveWorkbook), and it returns you the name you should save it as. From there, you can do the SaveAs with the name. There are a number of other issues in the post (how this gets triggered, etc), but this should get you a good name.

If it seems a little overly complicated, it is... I was trying to provide you some flexibility even if you say you don't want it :)

'This is how you'd call the function
Public Sub NewSaveAs()
Dim sNewName As String
sNewName = fGetNewSaveAsName(ActiveDocument)
If MsgBox("Good to go with: " & sNewName & "?", vbQuestion + vbYesNo, "New Save As Test") = vbYes Then
ActiveDocument.SaveAs2 sNewName
End If
End Sub
'return a valid name for a SaveAs process -- need slight modifications for Excel (Document to Workbook)
'as well as dealing with the AttachedTemplate bit or some other logic to determine if it is a
'new workbook or an existing workbook
'you can pass in different defaults for the prefix/suffix
Public Function fGetNewSaveAsName(oDoc As Document, _
Optional sPrefix As String = "1234", _
Optional sSuffix As String = "K") As String
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim oLastGoodFile As Object
Dim sNewName As String
Dim sPath As String
Dim iIncrement As Integer
Dim x As Integer

'get a reference to the current document or workbook
Set oDoc = ActiveDocument
'Set oDoc = ActiveWorkbook

'determine the folder to check.
'new documents save in the same location as the attached template
'existing document save in the same folder as they already exist
'you may need to change this logic, if you're templates for new documents don't reside
'in the same folder
If InStr(oDoc.Name, ".") = 0 Then
'this will need to change in excel as well- but I'm not sure to what
sPath = oDoc.AttachedTemplate.Path
Else
sPath = oDoc.Path
End If

'get an instance of the FileSystemObject
Set oFSO = CreateObject("Scripting.FileSystemObject")

'create a reference to the main revised files folder
Set oFolder = oFSO.GetFolder(sPath)

'cycle through all the files to determine our last "good" file
For Each oFile In oFolder.Files
'determine if this is a "good" file name by...
'seeing what the prefix is
If Left(oFile.Name, 4) = sPrefix Then
'and what the last letter before the doc.extension is
'see if it has a file extension (i.e., .doc, .docx, .xlsx, etc
x = InStr(oFile.Name, ".")
'if it does, check the character immediately preceeding
If x <> 0 Then
If Mid(oFile.Name, x - 1, 1) = sSuffix Then
Set oLastGoodFile = oFile
End If
End If
End If
Next
'if we have no last good file, then we don't have to test the increment
If oLastGoodFile Is Nothing Then
iIncrement = 1
Else
're-retrieve our extension location
x = InStr(oLastGoodFile.Name, ".")
'and the approporiate number
iIncrement = CInt(Mid(oLastGoodFile.Name, x - 3, 2))
'increment it up
iIncrement = iIncrement + 1
End If

'start by formatting our increment properly
sNewName = Format(iIncrement, "00")
'build the new name
sNewName = sPrefix & "-" & sNewName & sSuffix
'and return it to our function
fGetNewSaveAsName = sNewName
End Function

[rong_peng]

Thanks, I'll give it a try...:thumb