XL Workbook Properties
 

How can I print the Properties of an XL workbook as I can in Word?

Hi Chrisan,

I have this code in my Personal macro workbook that prints out the built-in
document properties for an Excel worksbook. It is based on an example in the
Excel help file:

Public Sub ListDocumentProperties()
On Error GoTo err_Handler

Dim xlWs As Excel.Worksheet
Dim p As Office.DocumentProperty
Dim rw As Long

rw = 2
Set xlWs = Worksheets.Add
xlWs.Name = "_Properties"
xlWs.Cells(1, 1).Value = "Property"
xlWs.Cells(1, 2).Value = "Value"
xlWs.Rows(1).EntireRow.Font.Bold = True

For Each p In ActiveWorkbook.BuiltinDocumentProperties
xlWs.Cells(rw, 1).Value = p.Name
xlWs.Cells(rw, 2).Value = p.Value
rw = rw + 1
Next p

xlWs.Columns("A:B").AutoFit
xlWs.Select
xlWs.PrintPreview

Application.DisplayAlerts = False
xlWs.Delete
Application.DisplayAlerts = True

Set p = Nothing
Set xlWs = Nothing
Exit Sub

err_Handler:
Select Case Err.Number
Case 1004 ' Sheet with same name already exists = delete it!
Application.DisplayAlerts = False
Application.ActiveWorkbook.Worksheets("_Properties ").Delete
Application.DisplayAlerts = True
Resume
Case -2147467259 ' Property value is unset = ignore and continue
Resume Next
Case Else
MsgBox Err.Number & vbCr & Err.Description
Debug.Assert 1 = 2
Resume
End Select
End Sub

NB if you have a worksheet called "_Properties" already in your workbook it
will be deleted!

Hope this helps.

Cheers,

Mark

"chrisan" wrote:

How can I print the Properties of an XL workbook as I can in Word?