How do I print Excel file properties information?

  • Thread starter Thread starter Andy Holm
  • Start date Start date
A

Andy Holm

I would like to print Excel file properties like it is possible to do for
Word documents to show date created, saved, etc. Is there a way to do this?
Thanks for the help.
 
Andy

With some code you can get the built-in Document Properties.

I don't know how extensive "etc" is but if you want to print out Custom
Properties also that would take much more code.

For built-ins you can use this UDF

Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

'=DOCPROPS("author")
'or
'=DOCPROPS("last save time")
'or
'DOCPROPS("creation date")

To get a list of all available built-ins on a new sheet run this macro.

Sub documentprops()
'list of properties on a new sheet
rw = 1
Worksheets.Add
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
Cells(rw, 4).Value = "=DocProps(" & "A" & rw & ")"
rw = rw + 1
Next
End Sub


Gord Dibben MS Excel MVP

On Sat, 16 Aug 2008 07:46:01 -0700, Andy Holm <Andy
 
Hi,

Right click the sheet tab where you want the report, view code and paste
this an run it

Sub Properties()
x = 1
Cells(x, 1).Value = "Properties"
x = x + 1
For Each p In ActiveWorkbook.BuiltinDocumentProperties
On Error Resume Next
Cells(x, 1).Value = p.Name
Cells(x, 2).Value = p.Value
x = x + 1
Next
On Error GoTo 0
End Sub


Miek
 
Gord
....if you want to print out Custom
Properties also that would take much more code.
Click to expand...

Unless I'm missing something for Custom properties don't you simply
substitute this
in my answer

For Each p In ActiveWorkbook.BuiltinDocumentProperties

with this

For Each p In ActiveWorkbook.CustomDocumentProperties

Mike
 
How about this macro:

Sub Macro1()
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterHeader = ""
.LeftHeader = ""
i = 1
For Each wp In ActiveWorkbook.BuiltinDocumentProperties
If i = 12 Or i = 11 Then
On Error Resume Next
.CenterHeader = .CenterHeader & wp.Name & " " & wp.Value &
Chr(10)
End If
i = i + 1
Next

End With
End Sub
 
Back
Top