Solved

Need Help with Strategy to Document Excel Workbooks

Posted on 2015-02-20
16
108 Views
Last Modified: 2016-02-12
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.
0
Comment
Question by:qeng
  • 9
  • 6
16 Comments
 

Assisted Solution

by:Jay Williams
Jay Williams earned 50 total points
ID: 40620958
Not sure if this will help, but this attachment uses a strategy that combines cell values with the date and the macros save to the value of A1.
PickListTemplate.xlsm
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 450 total points
ID: 40620981
Two strategies, both to do with document properties. One of the advantages of using this is that they are standard, and therefore no major customisation required.

Firstly, you could use the "Show Document Panel" to give you things such as Author, Title, Subject, Keywords, Category, Status and Comments. There are additional metadata such as Version Number etc. also available. See Accessing Microsoft Office document properties from File Menu for more details.

Secondly, and one of the advantages of using document properties, once they have been set, if you need to document them for an entire folder, you can do that with a metadata extractor.

Hope that's food for thought.
0
 

Author Comment

by:qeng
ID: 40622871
Jay, thanks for your input.  

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_with_some_numberIDs_12345_and_a_rev#                        2015-01-01


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.
0
 

Author Comment

by:qeng
ID: 40622883
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?)
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 450 total points
ID: 40625245
No, no, no and no.

Basically, what you need is a form in VBA, You can access the Microsoft document properties in VBA to populate and write to the form.
0
 

Author Comment

by:qeng
ID: 40625493
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.BuiltinDocumentProperties As Object (I tried this unsuccessfully)
Dim BuiltinDocumentProperties As Object '(this is also unsuccessful)

ActiveDocument.BuiltinDocumentProperties("Author") = "New person"
End Sub

I get the error:

Runtime error '424':
Object required

(I don't know how to define the required object)

How_to_Create_Custom_DIP_Template_and_Display_in_Excel_2010
0
 

Author Comment

by:qeng
ID: 40625499
My bad, I had to change 'ActiveDocument' to 'ActiveWorkbook', as per:

Sub myDIP()
ActiveWorkbook.BuiltinDocumentProperties("Author") = "New person"
End Sub

That works.

My question remains though in regard to creating and displaying a Custom DIP Template inside Excel.
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 450 total points
ID: 40625500
The weblink had the following as well:

"For Microsoft Excel, change ActiveDocument to ActiveWorkbook"

As you are in Excel, you need to use ActiveWorkbook.

Try that, and see if that works.

I don't believe there is a way to directly modify the DIP.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:qeng
ID: 40625502
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)
0
 

Author Comment

by:qeng
ID: 40625503
Sorry, our messages are out of sync.  I'd spotted that but you replied as I was posting.
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 450 total points
ID: 40625514
However, you can create a standalone form which has the metadata, and then all you need to do is a) associate it with whatever workbooks you want, and b) write the code to initially populate it, and to do any changes they make (if that's what you want to do).
0
 

Author Comment

by:qeng
ID: 40625559
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?
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 450 total points
ID: 40625567
I wouldn't. I would have either a custom button to open it.
0
 

Author Comment

by:qeng
ID: 40625590
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)?
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 450 total points
ID: 40625604
Just Floating - or you could have it as a dockable pane. See https://msdn.microsoft.com/en-us/library/aa338197(v=office.12).aspx for more information.
0
 

Author Closing Comment

by:qeng
ID: 40653832
Thanks for the focussed advice Phil.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now