How can I display worksheet summary page ie title/size/last updat.

[Guest]

I'm running MSExcel 2003.

I am often working with large workbooks with mutiple worksheets. It would be
v helpful if I could see/sort and print off a summary that shows worksheet
properties similar to Explorer eg:

workbook :xxxx
----------------
worksheet last update size
names 1/2/04 64k
companies: 5/2/03 893k
etc...

any ideas?

 

[Nick Hodge]

Jon

I wrote this sometime ago and it works as a workbook_open event (Needs to be
placed in the ThisWorkbook code module). It could be adapted to work
otherwise though. It adds a sheet and lists all the built-in and custom
properties on it

Private Sub Workbook_Open()
Dim oBuiltProp As DocumentProperty, wkSht As Worksheet
Dim oCustProp As DocumentProperty, r As Integer, rRange As Range
On Error Resume Next
r = 1
Set wkSht = Me.Worksheets.Add
wkSht.Name = "Properties"
Set rRange = wkSht.Range("A1")
With rRange
.Offset(0, 0).Value = "Name"
.Offset(0, 1).Value = "Value"
.Offset(0, 2).Value = "Type"
End With
For Each oBuiltProp In ThisWorkbook.BuiltinDocumentProperties
With rRange
.Offset(r, 0).Value = oBuiltProp.Name
.Offset(r, 1).Value = oBuiltProp.Value
.Offset(r, 2).Value = "Built in Property"
End With
r = r + 1
Next oBuiltProp
For Each oCustProp In ThisWorkbook.CustomDocumentProperties
With rRange
.Offset(r, 0).Value = oCustProp.Name
.Offset(r, 1).Value = oCustProp.Value
.Offset(r, 2).Value = "Custom Property"
End With
r = r + 1
Next oCustProp
wkSht.Columns("A:C").AutoFit
rRange.Select
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)