Access: Two Handy Procedures

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:

http://img.office-watch.com/waw/AW%201607-A.jpg image from Access: Two Handy Procedures at Office-Watch.com

Figure A. A QAT button to close all open Word documents

Windows 10 from people 'in the know'

A detailed and independent look at Windows 10, especially written for the many people who use Microsoft Office.

Fully up-to-date with coverage of the Anniversary 2016 major update of Windows 10.

This 670 page book shows you important features and details for all serious Windows 10 users.

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:

http://img.office-watch.com/waw/AW%201607-B.jpg image from Access: Two Handy Procedures at Office-Watch.com

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:

http://img.office-watch.com/waw/AW%201607-C.jpg image from Access: Two Handy Procedures at Office-Watch.com

Figure C. The Sort Declarations button in the QAT

Now the list of declarations is alphabetized:

http://img.office-watch.com/waw/AW%201607-D.jpg image from Access: Two Handy Procedures at Office-Watch.com

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