PDA

View Full Version : [SOLVED] Document workbook Properties



montrof
06-09-2005, 07:43 AM
I need something that can go through a workbook and create a report with the following information:

Owner
File location
Last accessed date
Tab names
Formulas and links by tab
Macros with the code
Thanks
Montrof

Jacob Hilderbrand
06-09-2005, 01:30 PM
This should get you started.



Option Explicit

Sub FileProperties()
Dim Owner As String
Dim Path As String
Dim AccessDate As Date
Dim ws As Worksheet
Dim Tabs As String
Owner = ActiveWorkbook.BuiltinDocumentProperties("Author")
Path = ActiveWorkbook.Path
AccessDate = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
For Each ws In Worksheets
Tabs = Tabs & ws.Name & vbNewLine
Next
MsgBox Owner & vbNewLine & _
Path & vbNewLine & _
AccessDate & vbNewLine & Tabs
End Sub


For 5 do you want all formulas and hyperlinks in each sheet?

montrof
06-09-2005, 01:35 PM
Thank you for the start. Is there a way to get all the vba modules and code and put them into a report and also the formulas that are used?

Jacob Hilderbrand
06-09-2005, 01:38 PM
List all formulas:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=409

List all macros:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=398

montrof
06-09-2005, 01:57 PM
great, how do i make it so that the results are posted to a worksheet instead of a message box?

MWE
06-10-2005, 09:33 AM
great, how do i make it so that the results are posted to a worksheet instead of a message box?
this depends on what you really want to do. If the data is for a single workbook, then simply post individual items to individual cells. For example, if the target worksheet is "BookData", then try:



Worksheets("BookData").Cells(1,1) = "Owner = " & Owner
Worksheets("BookData").Cells(2,1) = "Path = " & Path
Worksheets("BookData").Cells(3,1) = "AccessDate = " & Access
Worksheets("BookData").Cells(4,1) = "Tabs = " & Tabs

montrof
06-10-2005, 10:10 AM
Thanks for all the help!!!!!

Jacob Hilderbrand
06-10-2005, 10:25 AM
Glad to help. :beerchug:

Take Care