Link to Document Properties in Excel 4

[eman6]

Is there a way I can automatically display some document properties at the first sheet in Excel by links, like we could do in Word using Insert_Field_Document Information/Doc Property, etc?



_______________________________________

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[SkipVought]

Which properties?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[mp9]

Easily done from within VBA in Excel, e.g. activeworkbook.BuiltinDocumentProperties("Author") returns the contents of the Author document property, and so on.

http://www22.brinkster.com/accessory/

 

[eman6]

File_Properties

Such as Author, Category, department, etc



_______________________________________

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[eman6]

Unfortunately, I am not allowed to use VBA.
It must be a dynamic hyperlink to properties, if possible.


_______________________________________

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[SkipVought]

Apart from the CELL Spreadsheet Function (very limited), you're up the creek without a paddle if you can't use VBA.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[mp9]

There's also the INFO worksheet function, which will tell you about about your environment (e.g. number of sheets in active workbooks, operating system, Excel version, etc) but not the doc properties. Other than that, it's like SkipVought has said - without VBA your paddle/creek situation is not good, sorry.

http://www22.brinkster.com/accessory/

 

[combo]

You could use excel 4 GET.WORKBOOK macro function in names layer to get some (very limited) summary info, arguments:
16 - name of the workbook
33 - title
34 - subject
35 - author
36 - keywords
37 - comments

Insert name, say wbkAuthor, in refers to write =GET.WORKBOOK(35)&TEXT(NOW(),""). (The second part of formula forces recalculation).
Now you can use =wbkAuthor in a cell to display author.

combo

 

[SkipVought]

Cool, combo! ==>*

I can't find any documentation in help (xl2000) for the arguments.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[KenWright]

CAREFUL!!!!!!

Thread below reproduced from the MS newsgroups on this:-

"Stephen Dunn" wrote...
..
>WARNING, in versions of Excel earlier than 2k, cells containing macro
>formulae cannot be safely copied to other worksheets - they will crash
>Excel.
..

Make that earlier than Excel 2002/XP. Pasting formulas with names referring to XLM functions into different worksheets than that containing the copied cells
happily crashes Excel 2000 and 97. I'm not sure anyone has reported on what happens in 5/95 and earlier versions.

---------------------------------------------------------------------------
---------------


"Debra Dalgleish" <dsd@contextures.com> wrote...
>The following article was mentioned in a previous thread, and is still
>working:
>
>

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q151449

>
>Often a link will wrap in an email message, and you have to paste it
>back together before it works properly.
...

Ugh, even Microsoft recommends using XLM functions in defined names without warning that pasting cell formulas using those names into different worksheets will crash Excel 2000 and prior versions. And they use the OFFSET(INDIRECT(...)) idiom rather than just INDIRECT with R1C1 text references!
---------------------------------------------------------------------------
---------------

One caveat with respect to the GET.FORMULA responses. If you're using Excel 2000 or older, copying a cell which in any way references an XLM formula (such as GET.FORMULA) and trying to paste it into a different worksheet will *KILL* Excel *IMMEDIATELY* with *COMPLETE* *DATA* *LOSS*!

This has been fixed in Excel XP, but the other repondents should be aware that not all Excel users have upgraded to Excel XP (or even 2000, or even 97). So unless the OP has stated *explicitly* that they're using Excel XP, these
other respondents would be wise to remember to mention this caveat. In older versions of Excel, XLM functions in defined names are like renovating your home using plastic explosives.
----------------------------------------------------------------------------
--------------

Jan Karel Pieterse <pieterse_home@compuserve.com> wrote...
...
>Define this name (Insert, Name, Define):
>
>Name:
>Colour
>RefersTo:
>=GET.CELL(63,OFFSET(INDIRECT("rc",FALSE),0,-1,1,1))
...

BE VERY CAREFUL WITH THIS!

Using XLM functions in defined names in Excel 2000 & prior versions is dangerous. If you define such a name, use it in a cell formula, copy that cell and paste it into a cell in another worksheet, you WILL throw an unrecoverable application error. Meaning Excel WILL crash without giving you any chance to save your work. Apparently this has been fixed in Excel 2002/XP.

Now for some criticism. This is a useless use of OFFSET.
INDIRECT("RC[-1]",False) would be more efficient.

----------------------------------------------------------------------------
--------------

Jason Morin <jason.morin@us.exel.com> wrote...
...
>2. Go to Insert > Name > Define
>3. In the "Refers to" box:
>
>=IF(GET.CELL(63,!A2)=46,1,0)
...

Even though Microsoft suggests using XLM functions in defined names in KnowledgeBase articles, IT'S DANGEROUS IN ANY VERSION OF EXCEL BEFORE XP/2002. Copying cells containing references to named referring to formulas using XLM functions and pasting into ranges on other worksheets WILL CRASH EXCEL *INCLUDING* FULL DATA LOSS FOR YOU COMPUTING ENJOYMENT.

This piece of 'advice' should NEVER be given without this caveat.


----------------------------------------------------------------------------
--------------

Jan Karel Pieterse <pieterse_home@compuserve.com> wrote...
...
>A method that works if the pages go only dwonwards, not
>sideways:
>
>Define these names:
>
>RowAfterpgbrk =GET.DOCUMENT(64)
>TotPageCount =GET.DOCUMENT(50)
>PageOfPages ="Page "&IF(ISNA(MATCH(ROW(),
>RowAfterpgbrk,1)),1,MATCH(ROW(),RowAfterpgbrk,1)+1)&
>" of " & totpagecount + 0*now()
>
>Now use this formula:
>
>=PageOfPages ...
>Jan Karel Pieterse
>Excel TA/MVP

XLM function calls in defined names are oh so clever. However, in versions of Excel prior to 2002/XP, if you copy a cell with a formula referring to an XLM function - no matter how indirectly - then paste into another worksheet, Excel will crash with complete data loss. Try it out!

It's a mystery to me why I haven't yet succeeded in getting the XLM-philes to provide this warning whenever they suggest using XLM functions in defined names. Maybe they just don't care.

[Yes, I did note that the OP used 'worksheet' in the singular, but the OP isn't the only person who read this (I did too, FWIW), and others might try it in conjunction with copy/paste between worksheets.]
--
Public Service Announcement:
Don't attach files to postings in this newsgroup.


Regards
Ken......................................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Ken,

Now that's the Mother of all Caveats!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[KenWright]

LOL - Oh Yeah!!!

Personally I think I'd rather avoid the risk on that particular one.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

and the father, plus a few kids for good measure

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[combo]

Hi,
having in mind all the danger when using XLM macros in names, for anyone interested in that, it is still possible to download XLM help file from Microsoft.

combo

 

[eman6]

Thank you both, Combo and Ken for your useful hints.
We're using Excel 2002.

_______________________________________

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[KenWright]

You should be OK, but be aware if any of your stuff gets mailed out to customers or suppliers that are on earlier versions, you may well end up being unpopular

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------