Automatic Numbering of Documents

Home Up Search This Site What's New? Audio On CDR Favourites Downloadable files Photo Gallery 2002 Photo Gallery 2003 Photo Gallery 2004/5 Photo Gallery 2006/7 Photo Gallery 2008 Photo Gallery 2009/10 UK Photo Gallery Ireland Photo Gallery Cats Photo Gallery 

 

 

Google
 

 

Many people access the material from this web site daily. Most just leech what they want and run. That's OK, provided they are not selling on the material as their own; however if your productivity gains from the material you have used, a donation from the money you have saved, however small, would help to ensure the continued availability of this resource.

Click the appropriate button above to access PayPal.

 

Numbering documents for certificates, invoices, order forms etc.

Inserting a number from an ini file

 

The method below is inspired by code developed by fellow MVP Doug Robbins and reproduced in a web page he developed for the MVP FAQ site. The number sequence is stored in an editable text file called Settings.ini which is stored here in the Word startup folder and displayed by a docvariable field inserted in the header by the macro.

For a multi-user system any folder to which all users have read/write access can be used. Instead of using the Options.DefaultFilePath() you can enter the full path to the file.

The same Settings.ini file can be used to store number sequences for other macros as required.

The macros should be stored in a module in the document template - not in the normal template!

The macro code below will insert an incrementing number and and some fixed text at the right side of the document header thus:

The fixed text and the number format are defined in the two constants at the start of the macro.

Code to change the number and to recycle the number when a document is closed without saving is included.

Note

You will find another example of this technique on the vba macro examples page

 

Option Explicit
Private
CertNo As String
Private
oHeader As Range
Private oHead As HeaderFooter
Private oField As Field
Private sName, sPath As String
Privat
e oVars As Variables

Private bProtected As Boolean
Const sFormat As String = "000#"

Const sText As String = "Certificate No. "

Const sPassword As String = "" 'The password to edit the form

Sub AutoNew() 'Add a number when a new document is created
'If the document is a protected form, unprotect it
If ActiveDocument.ProtectionType <> wdNoProtection Then
     bProtected = True
     ActiveDocument.Unprotect _

     Password:=sPassword
End If

sPath = Options.DefaultFilePath(wdStartupPath) & "\Settings.ini"
Set oVars = ActiveDocument.Variables
CertNo = System.PrivateProfileString(sPath, _
"MacroSettings", "CertificateNumber")
If CertNo = "" Then 'The settings file entry does not exist
     CertNo = 1 'So set the start number
Else 'The settings file entry does exist so increment it by 1
     CertNo = CertNo + 1
End If
'Define two document variables of the same initial value
oVars("varCertNo").Value = CertNo
oVars("varSaveNo").Value = CertNo
'The number is to be placed in the header on the first page
'so establish which type of header that is.

If ActiveDocument.Sections(1).Headers(wdHeaderFooterFirstPage).Exists Then
     Set oHeader = ActiveDocument.Sections(1).Headers(wdHeaderFooterFirstPage).Range
Else
     Set oHeader = ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range
End If
'Setup the format of the header.
'Note that this will remove any existing header content
'So ensure you include anything else required in the header
'Here we have some fixed text, and assorted formatting that should
'be self evident.

With oHeader
     .Text = sText
     .Font.Name = "Times New Roman"
     .Font.Bold = True
     .Font.Italic = False
     .Font.Size = "16"
     .ParagraphFormat.Alignment = wdAlignParagraphRight
     .Collapse wdCollapseEnd
     'Insert a docvariable field to display the formatted number
     .Fields.Add oHeader, wdFieldDocVariable, _
         """varCertNo"" \# " & sFormat, False
     .Fields.Update
End With
'Save the number in the INI file
System.PrivateProfileString(sPath, _
"MacroSettings", "CertificateNumber") = CertNo
'If the document was unprotected initially,

' reprotect the document for forms.
If bProtected = True Then
     ActiveDocument.Protect _
     Type:=wdAllowOnlyFormFields,

     NoReset:=True, _

     Password:=sPassword
End If

End Sub


Sub SaveCertificateAs()
sPath = Options.DefaultFilePath(wdStartupPath) & "\Settings.ini"
Set oVars = ActiveDocument.Variables
CertNo = System.PrivateProfileString(sPath, _
"MacroSettings", "CertificateNumber")
If Not ActiveDocument.Path = "" Then
     'The document has previously been saved, so resave
     ActiveDocument.Save
Else 'The document has not been previously saved, so save it
     'In the current document folder with a name including the number

     With Dialogs(wdDialogFileSaveAs)
          .Name = "Certificate " _
             & Format(oVars("varSaveNo"), _
             sFormat)
          .Show
     End With
End If

'Close the document
ActiveDocument.Close
End Sub

Sub
AutoClose() 'Recycles number if the document was unsaved.'
sPath = Options.DefaultFilePath(wdStartupPath) & "\Settings.ini"
Set oVars = ActiveDocument.Variables
CertNo = System.PrivateProfileString(sPath, _
"MacroSettings", "CertificateNumber")
'Check if the document is unsaved
If ActiveDocument.Name Like "Document#*" Then
     'Offer the user the opportunity to save
     If MsgBox("This Certificate has not been saved." & vbCr & _
         "Do you want to save before closing?", _
         vbYesNo, "MacroSettings") = vbYes Then
          With Dialogs(wdDialogFileSaveAs)
               .Name = "Certificate " _
                  & Format(oVars("varSaveNo"), _
                  sFormat)
               .Show
          End With
     Else
' Close the document and recycle the number
          If CertNo = oVars("varCertNo") Then
               MsgBox "The current number " & _
                  "will be recycled.", vbOKCancel, _
                  "Recycle"
               System.PrivateProfileString(sPath, _
                 "MacroSettings", _

                 "CertificateNumber") = CertNo - 1
          End If
     End If

     ActiveDocument.Saved = True
End If
End Sub

Sub
ResetStartNo()
'Reset the number shown in the current document and record
'it as the last used number

sPath = Options.DefaultFilePath(wdStartupPath) & "\Settings.ini"
Set oVars = ActiveDocument.Variables
'Get the current number from the settings file
CertNo = System.PrivateProfileString(sPath, _
"MacroSettings", "CertificateNumber")
'Get the user to input the new number
CertNo = InputBox("Reset certificate number?", _
"Reset", CertNo)
'Record the input number as a docvariable
oVars("varSaveNo").Value = CertNo
'Save the input number
System.PrivateProfileString(sPath, _
"MacroSettings", "CertificateNumber") = CertNo
'Set the new number as the displayed variable content
oVars("varCertNo").Value = CertNo
'Update the header
For Each oHead In ActiveDocument.Sections(1).Headers
     If oHead.Exists Then
          For Each
oField In oHead.Range.Fields
               oField.Update
          Next oField
     End If
Next
oHead
End Sub
 

Note:

If you don't know what to do with macro listings see - Installing Macros From Listings