Implement auto-increment with Word macro

Question

I'm writing a Word/VBA macro for a document template. Every time a user saves/creates a new document from the template, the document needs an ID embedded in the text. How can I (as simple as possible) implement auto-increment for this ID? The ID is numeric.

The system has to have some kind of mechanism to avoid different documents getting the same IDs, but the load is very low. About 20 people will use this template (on our intranet), creating something like 20 new documents a week altogether.

I've toyed with the idea of having a text file that I lock and unlock from the macro, or call a PHP page with an SQLite database, but is there other, smarter solutions?

Note that I can't use UUID or GUID, since the IDs need to be usable by humans as well as machines. Our customers must be able to say over the phone: "... and about this, then, with ID 436 ...?"

Answer 1

You could handle this entirely through VBA using Word and Excel (or Access I suppose, but I have an unnatural aversion towards using Access).

First, create a new Excel workbook and store it in a location that you can access through your word document (mine is C:\Desktop\Book1.xls). You may even want to seed the values by entering a numeric value into cell A1.

In your word document, you would enter this into your Document_Open() subroutine:

Private Sub Document_Open()

Dim xlApp       As Excel.Application
Dim xlWorkbook  As Excel.Workbook
Dim xlRange     As Excel.Range
Dim sFile       As String
Dim LastID      As Integer
Dim NewID       As Integer

'Set to the location of the Excel "database"
sFile = "C:\Desktop\Book1.xls"

'Set all the variables for the necessary XL objects
Set xlApp = New Excel.Application
Set xlWorkbook = xlApp.Workbooks.Open(sFile)

'The used range assumes just one column in the first worksheet
Set xlRange = xlWorkbook.Worksheets(1).UsedRange

'Use a built-in Excel function to get the max ID from the used range
LastID = xlApp.WorksheetFunction.Max(xlRange)

'You may want to come up with some crazy algorithm for
'this, but I opted for the intense + 1
NewID = LastID + 1

'This will prevent the save dialog from prompting the user
xlApp.DisplayAlerts = False

'Add your ID somewhere in the document
ThisDocument.Range.InsertAfter (NewID)

'Add the new value to the Excel "database"
xlRange.Cells(xlRange.Count + 1, 1).Value = NewID

'Save and close
Call xlWorkbook.Save
Call xlWorkbook.Close

'Clean Up
xlApp.DisplayAlerts = True
Call xlApp.Quit
Set xlWorkbook = Nothing
Set xlApp = Nothing
Set xlRange = Nothing

End Sub

I realize this is a tall procedure, so by all means re-factor it to your heart's content. This was just a quick test I whipped up. Also, you'll need to add a reference to the Excel Object Library through References in VBA. Let me know if you have any questions about how that works.

Hope that helps!