The two procedures will let you easily clear up redundant Word documents, and alphabetize lists of declarations.
Access versions: 2007-2013
Level: Intermediate
Introduction
The two procedures described in this article will let you easily clear up redundant Word documents, and alphabetize lists of declarations.
When working on the 3rd edition of my Working with Word ebook (soon to be released by Office Watch), I generated a great many Word documents, and it was tedious to close them down. Selecting Close all windows from the Word taskbar icon pop-up menu doesn’t always close all open Word documents – it is likely to make you choose whether or not to save some documents, which is annoying. I wanted a way to automatically close all open Word documents, saving those that had been modified. Here is the procedure that does this:
VBA Code
Public Function CloseAllWordDocs()
On Error GoTo ErrorHandler
Dim appWord As Word.Application
Dim doc As Word.Document
Dim intCounter As Integer
Dim lngCount As Long
Dim lngItem As Long
Set appWord = GetObject(, “Word.Application”)
lngCount = appWord.Documents.Count
If lngCount = 0 Then
GoTo ErrorHandlerExit
Else
‘Work backwards to prevent skipping any items
For lngItem = lngCount To 1 Step -1
Set doc = appWord.Documents(lngItem)
‘Debug.Print “Examining ‘” & doc.Name & “‘”
If Left(doc.Name, 8) = “Document” Then
doc.Close savechanges:=wdDoNotSaveChanges
Else
doc.Close savechanges:=wdSaveChanges
End If
intCounter = intCounter + 1
Next lngItem
‘Debug.Print intCounter & ” documents closed”
End If
ErrorHandlerExit:
Set appWord = Nothing
Exit Function
ErrorHandler:
If Err = 429 Then
‘Word is not running; open Word with CreateObject
Set appWord = CreateObject(“Word.Application”)
Resume Next
Else
MsgBox “Error No: ” & Err.Number _
& ” in CloseAllWordDocs procedure; Description: ” _
& Err.Description
Resume ErrorHandlerExit
End If
End Function
The code sets up a variable referencing the Word Application object, and counts the number of open documents; if there are none, the code exits. If some open documents are found, a For…Next loop works backwards through the open documents (this is done to avoid skipping documents, which can happen when working forwards), closing any documents called “Document*” without saving, and closing and saving any other documents. Two commented out Debug.Print statements can be uncommented to display the progress of the procedure in the Immediate Window.
For convenience, I made a macro called mcrCloseAllWordDocs to run this procedure, and placed this macro on the Quick Access Toolbar, so any time I want to close down a bunch of open Word documents, I just click this button:
Figure A. A QAT button to close all open Word documents
The second handy procedure sorts lists of declarations in code. This is handy, because it makes it easier to find a specific reference, or to see that there are duplicate references. Since there is no way to sort declarations within Access, I use the Word Sort feature for this purpose, and the DataObject from the MS Forms object library to retrieve data from the clipboard. Finally, the sorted declarations list is pasted back into the module using the ancient SendKeys command. Here is the procedure:
Public Function SortDeclarations()
On Error GoTo ErrorHandler
Dim appWord As Word.Application
Dim dat As MSForms.DataObject
Dim doc As Word.Document
Dim strPaste As String
Dim strText As String
Set dat = New MSForms.DataObject
dat.GetFromClipboard
strText = dat.GetText
‘Debug.Print “Copied text: ” & strText
Set appWord = GetObject(, “Word.Application”)
appWord.Visible = False
Set doc = appWord.Documents.Add
doc.Select
With appWord.Selection
.TypeText strText
.WholeStory
.Sort ExcludeHeader:=False, _
FieldNumber:=”Paragraphs”, _
SortFieldType:=wdSortFieldAlphanumeric, _
SortOrder:=wdSortOrderAscending, _
Separator:=wdSortSeparateByTabs, _
SortColumn:=False, _
CaseSensitive:=False, _
LanguageID:=wdEnglishUS, _
SubFieldNumber:=”Paragraphs”
.HomeKey Unit:=wdStory
.Delete Unit:=wdCharacter, Count:=1
.WholeStory
.Copy
End With
doc.Close savechanges:=wdDoNotSaveChanges
strPaste = “%{F11}”
SendKeys strPaste
strPaste = “%EP”
SendKeys strPaste
ErrorHandlerExit:
Set dat = Nothing
Set appWord = Nothing
Exit Function
ErrorHandler:
If Err = 429 Then
‘Word is not running; open Word with CreateObject
Set appWord = CreateObject(“Word.Application”)
Resume Next
Else
MsgBox “Error No: ” & Err.Number _
& ” in SortDeclarations procedure; ” _
& “Description: ” & Err.Description
Resume ErrorHandlerExit
End If
End Function
As with the CloseAllWordDocs procedure, I made a macro to run the procedure (mcrSortDeclarations), and placed it on the QAT. To sort a list of declarations, highlight the list in the code module and press Ctrl-C to copy it to the clipboard:
Figure B. A list of declarations in need of alphabetization
Then switch to the main Access window and click the Sort Declarations button in the QAT:
Figure C. The Sort Declarations button in the QAT
Now the list of declarations is alphabetized:
Figure D. The alphabetized Declarations list
Installation
At some point I may incorporate these procedures into an add-in, but for now, if you wish to use one or more of them in a database, import the saved module and macros into the database, set references to the Word object model and the MS Forms 2.0 object model, and make buttons for the procedures on the QAT. The module also includes the BringDocToFront procedure, mentioned in a response to a reader’s question.
References
The code in the sample database needs the following references (in addition to the default references):
Microsoft Word 11.0 Object Library
MS Forms 2.0 Object Library
If you import code or objects into a database of your own, you may need to set one or more of these references. The version number may differ, depending on your Office version; check the version you have. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
Supporting Files
The zip file containing this article, in Word 97-2003 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site, as accarch231.zip, which is the last entry in the table of Access Archon columns for Access Watch.
Document Name |
Document Type |
Place in |
Two Handy Procedures (AA 231).accdb |
Access 2007-2010 database |
Anywhere you want |