The Document Inspectors Collection

The DocumentInspectors collection of the Workbook object contains a group of DocumentInspector objects that provide a VBA interface to the features exposed by the last five sections of the Document Inspector user interface. These features and their corresponding index numbers in the DocumentInspectors collection are shown here:


— Custom XML Data


— Headers and Footers


— Hidden Rows and Columns


— Hidden Worksheets


— Invisible Content

Individual DocumentInspector objects can only be accessed by index number from the DocumentInspectors collection, or by iterating the entire collection with a For...Each loop.

There are a number of differences between capabilities of the RemoveDocumentInformation method and the DocumentInspector object. These differences are summarized as follows:

□ The DocumentInspector object has an Inspect method that can be used to passively determine whether a workbook contains any information that would be removed by the object, without actually doing so.

□ Using the Fix method of the DocumentInspector object to remove affected information is an all-or-nothing process. For example, if you choose to remove headers and footers using the DocumentInspector object designed for that purpose, all headers and footers in all worksheets in the workbook will be removed. There is no way to limit the removal to headers or footers alone.

□ If the Fix method of the DocumentInspector object fails as a result of the workbook containing protected worksheets, or for any other reason, it will simply return an error code and description of the problem rather than causing a VBA run-time error.

The following sample code shows how you would loop through all .xlsx format workbooks in the C:\Files folder and use a DocumentInspector object to remove all hidden rows and columns from those workbooks. If the DocumentInspector object is unable to perform its task on a workbook, the code will display a message box with the reason for the failure:

Sub RemoveHiddenRowsAndColumns()

Dim objDI As DocumentInspector Dim uStatus As MsoDocInspectorStatus Dim strResult As String Dim strPath As String

Dim strFileName As String Dim wkbBook As Workbook

Application.ScreenUpdating = False strPath = "C:\Files\"

strFileName = Dir$(strPath & "*.xlsx")

Do While Len(strFileName) > 0

Set wkbBook = Workbooks.Open(strPath & strFileName) Set objDI = wkbBook.DocumentInspectors(3)

objDI.Fix uStatus, strResult

If uStatus = msoDoclnspectorStatusError Then

' If the Fix method could not complete, display the error. MsgBox wkbBook.Name & ": " & strResult, vbExclamation


' Otherwise save the changes made to the workbook. wkbBook.Save End If wkbBook.Close False strFileName = Dir$()


Application.ScreenUpdating = True End Sub

As demonstrated in this code sample, the two arguments to the DocumentInspector.Fix method are actually return values. After the method has attempted to run, these arguments tell you what happened and why.

0 0

Post a comment