vba 用 Word 宏实现自动递增
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/730052/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Implement auto-increment with Word macro
提问by Christian Davén
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.
我正在为文档模板编写 Word/VBA 宏。每次用户从模板保存/创建新文档时，该文档都需要在文本中嵌入一个 ID。我如何（尽可能简单）为这个 ID 实现自动递增？ID 是数字。
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.
系统必须有某种机制来避免不同的文档获得相同的 ID，但负载非常低。大约 20 人将使用此模板（在我们的内部网上），每周总共创建大约 20 个新文档。
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?
我曾想过拥有一个可以从宏中锁定和解锁的文本文件，或者使用 SQLite 数据库调用 PHP 页面，但是还有其他更智能的解决方案吗？
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 ...?"
请注意，我不能使用 UUID 或 GUID，因为这些 ID 需要可供人类和机器使用。我们的客户必须能够通过电话说：“……那么，关于这个，ID 436 ……？”
采纳答案by Christian Davén
It seems I found a way to open and update a text file with exclusive rights, which means that there will be no concurrency problems:
看来我找到了一种打开和更新具有独占权限的文本文件的方法，这意味着不会出现并发问题：
Private Function GetNextID(sFile As String) As Integer
Dim nFile As Integer
nFile = FreeFile
On Error Resume Next
Open sFile For Binary Access Read Write Lock Read Write As #nFile
If Err.Number <> 0 Then
' Return -1 if the file couldn't be opened exclusively
GetNextID = -1
Err.Clear
Exit Function
End If
On Error GoTo 0
GetNextID = 1 + Val(Input(LOF(nFile), #nFile))
Put #nFile, 1, CStr(GetNextID)
Close #nFile
End Function
Simply call this function until it doesn't return -1 anymore. Neat.
只需调用此函数，直到它不再返回 -1 为止。整洁的。
回答by TimS
Gave some further thought to this, and here is another approach you may want to consider. If you're not interested in a catalog of previous IDs, then you could simply use a custom document property to store the last ID that was used.
对此进行了一些进一步的思考，这是您可能需要考虑的另一种方法。如果您对以前 ID 的目录不感兴趣，那么您可以简单地使用自定义文档属性来存储最后使用的 ID。
In Word 97-2003, you can add a custom property by going to "File / Properties", choosing the custom tab and assigning a name and value there. Adding a custom document property in Word 2007 is a bit more buried and off the top of my head, I think it's "Office Button / Prepare / Document Properties", choose the little drop down box for advanced properties and you'll get the same ol' pre-2007 dialog.
在 Word 97-2003 中，您可以通过转到“文件/属性”，选择自定义选项卡并在那里分配名称和值来添加自定义属性。在 Word 2007 中添加自定义文档属性有点深藏不露，我认为它是“Office 按钮/准备/文档属性”，选择高级属性的小下拉框，你会得到相同的ol' 2007 年之前的对话。
In the example below, I called mine simply "DocumentID" and assigned it an initial value of zero.
在下面的示例中，我简单地称我的为“DocumentID”，并为其分配了初始值为零。
The relevant bit of code to update a Custom document property is:
更新自定义文档属性的相关代码是：
ThisDocument.CustomDocumentProperties("DocumentID").Value = NewValue
As a proof of concept, I created a .dot file and used the following code in the Document_New() event:
作为概念证明，我创建了一个 .dot 文件并在 Document_New() 事件中使用了以下代码：
Sub UpdateTemplate()
Dim Template As Word.Document
Dim NewDoc As Word.Document
Dim DocumentID As DocumentProperty
Dim LastID As Integer
Dim NewID As Integer
'Get a reference to the newly created document
Set NewDoc = ActiveDocument
'Open the template file
Set Template = Application.Documents.Open("C:\Doc1.dot")
'Get the custom document property
Set DocumentID = Template.CustomDocumentProperties("DocumentID")
'Get the current ID
LastID = DocumentID.Value
'Use any method you need for determining a new value
NewID = LastID + 1
'Update and close the template
Application.DisplayAlerts = wdAlertsNone
DocumentID.Value = NewID
Template.Saved = False
Template.Save
Template.Close
'Remove references to the template
NewDoc.AttachedTemplate = NormalTemplate
'Add your ID to the document somewhere
NewDoc.Range.InsertAfter ("The documentID for this document is " & NewID)
NewDoc.CustomDocumentProperties("DocumentID").Value = NewID
End Sub
Good luck!
祝你好运！
回答by TimS
You could handle this entirely through VBA using Word and Excel (or Access I suppose, but I have an unnatural aversion towards using Access).
您可以使用 Word 和 Excel（或者我想是 Access，但我对使用 Access 有一种不自然的厌恶）完全通过 VBA 来处理这个问题。
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.
首先，创建一个新的 Excel 工作簿并将其存储在您可以通过 Word 文档访问的位置（我的是 C:\Desktop\Book1.xls）。您甚至可能希望通过在单元格 A1 中输入数值来为值设定种子。
In your word document, you would enter this into your Document_Open() subroutine:
在您的 Word 文档中，您可以将其输入到 Document_Open() 子例程中：
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.
我意识到这是一个艰巨的过程，所以一定要根据您的内心情况重新考虑它。这只是我做的一个快速测试。此外，您还需要通过 VBA 中的引用添加对 Excel 对象库的引用。如果您对它的工作原理有任何疑问，请告诉我。
Hope that helps!
希望有帮助！
回答by DJ.
Well you have to store the next ID number somewhere. The text file idea is as good as any. You just have to handle the possibility of it being locked or unaccessible for some reason.
那么你必须在某个地方存储下一个 ID 号。文本文件的想法和任何想法一样好。您只需要处理它因某种原因被锁定或无法访问的可能性。
Using a database for one number is overkill.
将数据库用于一个数字是矫枉过正的。
回答by guillermooo
Off the top of my head:
在我的头顶：
- Use Excel as your external DB with Automation.
- Explore the several SQLite COM wrappers(Litex comes to mind).
- 使用 Excel 作为带有自动化功能的外部数据库。
- 探索几个SQLite COM 包装器（想到了 Litex）。
回答by jpinto3912
"text file that I lock and unlock from the macro" would be the safest approach. The DOCID file would only have one number: the last ACTUALLY used ID.
“我从宏中锁定和解锁的文本文件”将是最安全的方法。DOCID 文件只有一个数字：最后一个实际使用的 ID。
A) You read the file (not in write/append mode) and store on a variable on your document DOC_ID =FILE_ID+1 and save the doc. Tentatively you kill the DOCID file, open/create for read-write sotring your DOC_ID. Close the file. If all went well including Close, you're safe, otherwise, back to A).
A）您读取文件（不是在写入/追加模式下）并存储在文档 DOC_ID =FILE_ID+1 上的变量中并保存文档。暂时你杀死 DOCID 文件，打开/创建你的 DOC_ID 以进行读写排序。关闭文件。如果一切顺利，包括关闭，你就安全了，否则，回到 A)。
You might want to consider: if no file is found create it with this document ID +100, as a measure of recovering from no-UPS disasters whilst in A)
您可能需要考虑：如果没有找到文件，请使用此文档 ID +100 创建它，作为在 A 中从无 UPS 灾难中恢复的一种措施）
I'm too tired to check if it might create a deadlock under concurrency scenario... it might.
我太累了，无法检查它是否会在并发情况下造成死锁......它可能会。
If you feel its worth it, I can put code here.
如果你觉得值得，我可以把代码放在这里。
