File Name Automatic Number Increase

Question

Is it possible to have a template in either word or excel automaticaly open it with a file name that increases in a continuous order?

Answer 1

Yes, you can do it in both apps

Using Sequential Document Serial Numbers

http://word.tips.net/T001747_Using_Sequential_Document_Serial_Numbers.html

Auto-incrementing Form Fields – Serial Number

http://word.tips.net/T001329_Autoincrementing_Form_Fields.html

Using sequential numbers – Serial Number

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

Generating Unique Numbers for Worksheets – Serial Number

http://excel.tips.net/T003336_Generating_Unique_Numbers_for_Worksheets.html

Summary: You may need to automatically generate unique numbers when you create new worksheets in a workbook. Here's a couple of easy ways to do it. (This tip works with Microsoft Excel 97, Excel 2000, Excel 2002, Excel 2003, and Excel 2007.)

Answer 2

One method...............save a sequential number in the Registry then when you open a new workbook from the Template it is immediately saved as template name + sequential number pulled from the Registry

Example code for Thisworkbook module of an Excel template named MyTemplate.

NOTE: MyTemplate must be saved as macro-enabled template (*.xltm)

Private Sub Workbook_Open()

    Const sAPPLICATION As String = "Excel"

    Const sSECTION As String = "MyTemplate"

    Const sKEY As String = "MyTemplate_key"

    Const nDEFAULT As Long = 1&

    Dim nNumber As Long

    nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs sSECTION & Format(nNumber + 1&, "_0000")

    Application.DisplayAlerts = True

    SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&

End Sub

The first new workbook will be saved as Excel workbook MyTemplate_0001.xlsx then increment from there.........probably no need to have it as macro-enabled.  If you did need the copy saved as macro-enabled post back for revised code to saveas macro-enabled and prevent Workbook_Open from running when copies are opened later for review.

Gord

Answer 3

Templates are very handy for creating new workbooks with the formatting and

layout desired but if Template has workbook_open code you don't want it

running when you re-open the workbook after it has been saved.

To prevent code running when you re-open a saved workbook created from a

Template, check for a path.

A saved workbook will have a path.

An unsaved workbook will not.

Check for the path and Exit Sub if path is found.

If not, carry on with workbook_open code.

Private Sub Workbook_Open()

    Const sAPPLICATION As String = "Excel"

    Const sSECTION As String = "MyTemplate"

    Const sKEY As String = "MyTemplate_key"

    Const nDEFAULT As Long = 1&

    Dim nNumber As Long

If ActiveWorkbook.Path <> "" Then Exit Sub

    nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT)

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs sSECTION & Format(nNumber + 1&, "_0000")

    Application.DisplayAlerts = True

    SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1&

End Sub

NOTE: MyTemplate must be saved as macro-enabled template (*.xltm)

Gord

Answer 4

Fantastic code! Just what I have been looking for...

Could really use the update to save mas macro enabled workbook if that is possible - Ian Ashpole