Show document properties on sheet
Hiya,
I'm trying to find out how to show the document properties of an excel spreadsheet within the sheet.
I want to be able to set this up so that for example when the document is printed it prints the Version number too, which is taken from the properties of the document...
This appears easy to do in Word 2003 by using Insert Field...but I can't find a similar feature in Excel 2003...
Any suggestions or ideas, is VB required to do this?
Not sure what your looking for but try looking in Help for:
=INFO()
It has several options:
=INFO("osversion")
=INFO("release")
=INFO("system")
You can install this custom UDF and then use it to get many different Document Properties to appear in a cell:
========
Please Login or Register to view this content.
How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet
The function is installed and ready to use.
========
In a cell use as:
=DocInfo("Last Save Time")
Title
Subject
Author
Keywords
Comments
Last author
Revision number ??
Application name
Last print date
Creation date
Last save time
Security
Category
Format
Manager
Company
Hyperlink base
Total Editing Time
revision number seems to rework, but returns a zero for me. So maybe there's an additional setting somewhere for that.
Last edited by JBeaucaire; 11-17-2009 at 06:28 PM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Ok, it appears the revision # property is one you may have to set yourself in your sheets.
Inside the VBEditor, put this code into the THISWORKBOOK module:
This will set the "Total Editing Time" and the "Revision Number" flags every time you save you workbook. That means the answers would then be available to you via the UDF I gave you above.Please Login or Register to view this content.
JBeaucaire's posting #3 of 09-11-17, 06:15 PM was great help to me. I'm glad I found this site!
~ Donna
Glad it helped. And welcome to the Forum. Come on in, the water is fine.
UPDATE TO ALL - one of the macros in post #4 was in slight error. Here's the corrected code for the ThisWorkbook module to properly track the TOTAL EDITING TIME.
Please Login or Register to view this content.
Is there a way to show the current author or user, using this method?
You can use the Environ method to capture the current user. Search on that.
Can anyone advise. I have the same question as Nuttychick above but want to put the document properties into the footer. The VBA doesn't appear to work in the footer, I guess it doesn't behave like a cell. Any ideas.
Thanks.
Welcome to the Forum, chadders, unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
this works great if I'm tying to access the document properties for the local properties however I am not able to access the server document properties that are linked to SharePoint for that document. can you please help me access the SharePoint server document properties for that excel file?
sraoof,
Please check post 11.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks