qeng
asked on
Need Help with Strategy to Document Excel Workbooks
Using:
Excel 2010
I'm trying to build in some workbook/worksheet identifiers, such as workbook title, worksheet title, rev #, last updated date, worksheet developer name, etc. at the top of each worksheet in a workbook used by many people in an organization.
Putting that information in the top few rows of each worksheet is easy enough but then I end up fighting the column width issues and constantly moving the header information around to suit the visual formatting (in some cases it just doesn't work out, if for whatever reason say my first column in a given worksheet needs to be very wide).
I can alternatively create a worksheet I can call, say, 'Cover' and keep that type of metadata info on that sheet but that doesn't help users when they are on a given worksheet (they'd have to go clicking back and forth between the worksheet and cover page for example to click on the worksheet developer's name or check the worksheet revision number etc.)
I can also put that info into the Print Header but then it's not visible to users and updates are less likely to be made to the hidden Print Header information.
Is there some other more elegant strategy I can use to accomplish what I want to do?
What I'm looking for is more or less equivalent to have the first few rows of a given worksheet totally independent from the rows below so that resizing the width of a column in the first few rows doesn't affect the width of the columns below (I know I can't do that but am looking for a workaround strategy). Merging as and where needed is not practical since, with some merged columns in the top few rows, every time a column needs to be added or deleted in the rows below, it causes issues, especially for less experienced users.
I realize this would be easier to do with a custom designed MS Access solution but that isn't practical in this case to use that approach every time I want to provide some informational structure to corporate workbooks.
Excel 2010
I'm trying to build in some workbook/worksheet identifiers, such as workbook title, worksheet title, rev #, last updated date, worksheet developer name, etc. at the top of each worksheet in a workbook used by many people in an organization.
Putting that information in the top few rows of each worksheet is easy enough but then I end up fighting the column width issues and constantly moving the header information around to suit the visual formatting (in some cases it just doesn't work out, if for whatever reason say my first column in a given worksheet needs to be very wide).
I can alternatively create a worksheet I can call, say, 'Cover' and keep that type of metadata info on that sheet but that doesn't help users when they are on a given worksheet (they'd have to go clicking back and forth between the worksheet and cover page for example to click on the worksheet developer's name or check the worksheet revision number etc.)
I can also put that info into the Print Header but then it's not visible to users and updates are less likely to be made to the hidden Print Header information.
Is there some other more elegant strategy I can use to accomplish what I want to do?
What I'm looking for is more or less equivalent to have the first few rows of a given worksheet totally independent from the rows below so that resizing the width of a column in the first few rows doesn't affect the width of the columns below (I know I can't do that but am looking for a workaround strategy). Merging as and where needed is not practical since, with some merged columns in the top few rows, every time a column needs to be added or deleted in the rows below, it causes issues, especially for less experienced users.
I realize this would be easier to do with a custom designed MS Access solution but that isn't practical in this case to use that approach every time I want to provide some informational structure to corporate workbooks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Phillip,
Very slick (I'd forgotten about that panel).
Four questions:
How can I add my own metadata to the Document Information Panel (DIP)?
Issue: When I add a custom metadata field, it shows up in the properties list Properties > Advanced Properties > Custom but my custom-added field doesn't show up in the DIP which is visible in the document.
How can I get the DIP to automatically open each time a particular workbook is opened without forcing all other open workbooks to also have an opened DIP?
Issue: This seems to be the default behaviour, if I set the current workbook to display its DIP, any other workbooks I have open at the time, also have their DIP open.
How can I prevent some of the standard fields (e.g. 'Title') from displaying in the DIP?
Issue: Some of the standard metadata fields contained in the DIP won't be used in my workbooks or will tend to cause confusion with the users. For example, 'Title', since it sits right next to 'Author' is subject to being used as 'PositionTitle' (such as Manager), whereas I would want to use it as 'WorkBook Title'
Can the DIP layout and content be customized?
Issue: In addition to turning off the display of certain standard metadata fields (e.g. Category) and adding new custom metadata fields (e.g. WorkBookTitle), I would like to change the formatting (which fields are displayed where), fonts, and potentially add images or hyperlinked icons or macro buttons in the DIP space. Is there a way to do some or all of that (e.g. through VBA?)
Very slick (I'd forgotten about that panel).
Four questions:
How can I add my own metadata to the Document Information Panel (DIP)?
Issue: When I add a custom metadata field, it shows up in the properties list Properties > Advanced Properties > Custom but my custom-added field doesn't show up in the DIP which is visible in the document.
How can I get the DIP to automatically open each time a particular workbook is opened without forcing all other open workbooks to also have an opened DIP?
Issue: This seems to be the default behaviour, if I set the current workbook to display its DIP, any other workbooks I have open at the time, also have their DIP open.
How can I prevent some of the standard fields (e.g. 'Title') from displaying in the DIP?
Issue: Some of the standard metadata fields contained in the DIP won't be used in my workbooks or will tend to cause confusion with the users. For example, 'Title', since it sits right next to 'Author' is subject to being used as 'PositionTitle' (such as Manager), whereas I would want to use it as 'WorkBook Title'
Can the DIP layout and content be customized?
Issue: In addition to turning off the display of certain standard metadata fields (e.g. Category) and adding new custom metadata fields (e.g. WorkBookTitle), I would like to change the formatting (which fields are displayed where), fonts, and potentially add images or hyperlinked icons or macro buttons in the DIP space. Is there a way to do some or all of that (e.g. through VBA?)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Phillip,
I'm confused. It's not specifically the DIP data that I'm after at the moment (that will come later), it's modifying the DIP's data fields, and changing how they are presented inside the Excel Workbook that I'm trying to do.
Is there no way (with or without InfoPath) to create a Custom Document Interface Panel Template and display that template in an Excel Workbook's DIP?
The feature: Developer > Document Panel > [Specify a custom template (URL, UNC, or URN)] suggests that a custom template can be associated with the Workbook. (see embeded image)
Per your link, when I try using VBA (to test if I can change the Field Title "Author" in the DIP to "New Person", using:
Sub myDIP()
'Dim ActiveDocument As Object (I tried this unsuccessfully)
'Dim ActiveDocument.BuiltinDocu
Dim BuiltinDocumentProperties As Object '(this is also unsuccessful)
ActiveDocument.BuiltinDocu
End Sub
I get the error:
Runtime error '424':
Object required
(I don't know how to define the required object)
I'm confused. It's not specifically the DIP data that I'm after at the moment (that will come later), it's modifying the DIP's data fields, and changing how they are presented inside the Excel Workbook that I'm trying to do.
Is there no way (with or without InfoPath) to create a Custom Document Interface Panel Template and display that template in an Excel Workbook's DIP?
The feature: Developer > Document Panel > [Specify a custom template (URL, UNC, or URN)] suggests that a custom template can be associated with the Workbook. (see embeded image)
Per your link, when I try using VBA (to test if I can change the Field Title "Author" in the DIP to "New Person", using:
Sub myDIP()
'Dim ActiveDocument As Object (I tried this unsuccessfully)
'Dim ActiveDocument.BuiltinDocu
Dim BuiltinDocumentProperties As Object '(this is also unsuccessful)
ActiveDocument.BuiltinDocu
End Sub
I get the error:
Runtime error '424':
Object required
(I don't know how to define the required object)
ASKER
My bad, I had to change 'ActiveDocument' to 'ActiveWorkbook', as per:
Sub myDIP()
ActiveWorkbook.BuiltinDocu
End Sub
That works.
My question remains though in regard to creating and displaying a Custom DIP Template inside Excel.
Sub myDIP()
ActiveWorkbook.BuiltinDocu
End Sub
That works.
My question remains though in regard to creating and displaying a Custom DIP Template inside Excel.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wait, I'm going too fast here ... the above sub only added the name 'New person' into the Author field.
I was trying to change the label 'Author' to 'New person' (just as a means of testing the customization of the DIP)
I was trying to change the label 'Author' to 'New person' (just as a means of testing the customization of the DIP)
ASKER
Sorry, our messages are out of sync. I'd spotted that but you replied as I was posting.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That sounds like where I'm trying to go, Phil.
Would this standalone form then replace the default DIP template in a workbook in which it was associated, when the user opens that workbook's DIP?
If so, how would I go about that?
Would this standalone form then replace the default DIP template in a workbook in which it was associated, when the user opens that workbook's DIP?
If so, how would I go about that?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Would the custom DIP template then open in the DI Panel area if the button was activated? If not, where would that form open (or does it just end up floating above the worksheet)?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the focussed advice Phil.
ASKER
Unfortunately, unless I missed something, your proposed solution leaves me with the same problem I'm trying to resolve, namely that as soon as someone moves columns around or inserts new columns somewhere where my WorkSheet Header is visible, its structure and formatting fall apart.
As an example, imagine a workbook where my WorkSheet Header has the first three fields as ColumnA: 'Author', ColumnB: 'FirstName', ColumnC:'LastName' in row 1. Let's say it looks like this:
Author: Fred Flintstone
If someone decides to add in row 2 a new wide 'Document Title' column, it wreaks havoc with my WorkSheet Header such as
Author: Fred Flintstone
Doc ID DocTitle RevDate
001 AVeryLongDocumentTitle_wit
When I use the term 'WorkSheet Header' (not sure what to call it) in this context, we understand that this doesn't refer to the Page Header used in Printing, although the two could subsequently be related, for example, automating the Page Header to print the WorkSheet Header. That will be the next step if I can get a proper WorkSheet Header working.
Please correct me if I've misunderstood how I was to use your recommended approach. Merging and unmerging cells gets tedious when our users would have hundreds of workbooks in use.