Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Link to Document Properties in Excel 4

Status
Not open for further replies.
eman6

eman6

Programmer
Dec 7, 2004
578
CH
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
 
Sort by date Sort by votes

Which properties?

Skip,

[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue]
 
Upvote 0 Downvote
File_Properties

Such as Author, Category, department, etc



_______________________________________

Eman_2005
Technical Communicator
 
Upvote 0 Downvote
Unfortunately, I am not allowed to use VBA.
It must be a dynamic hyperlink to properties, if possible.


_______________________________________

Eman_2005
Technical Communicator
 
Upvote 0 Downvote

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

Skip,

[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue]
 
Upvote 0 Downvote
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.

[pc2]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote

Cool, combo! ==>*

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

Skip,

[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue]
 
Upvote 0 Downvote
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:
>
> >
>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......................................


----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Upvote 0 Downvote

Ken,

Now that's the Mother of all Caveats!

Skip,

[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue]
 
Upvote 0 Downvote
LOL - Oh Yeah!!!

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

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Thank you both, Combo and Ken for your useful hints.
We're using Excel 2002.

_______________________________________

Eman_2005
Technical Communicator
 
Upvote 0 Downvote
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.............

----------------------------------------------------------------------------
[peace]It's easier to beg forgiveness than ask permission[2thumbsup]
----------------------------------------------------------------------------
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BugZap13
  • Locked
  • Question
Excel formula to count occurrences of a value in a different sheet
Replies
5
Views
1K
bs6600
bs6600
Swi
  • Locked
  • Question
Excel Webservice
Replies
2
Views
771
mintjulep
M
Kennelbloke
  • Locked
  • Solved
Excel Unique-Filter issue
Replies
3
Views
729
SkipVought
S
bkoopers
  • Locked
  • Question
Problem with Excel pasting into filtered cells
Replies
1
Views
605
combo
combo
Kim296
  • Question
Need Genius Help - Finding multiple duplicates between columns - EXCEL 1
2
Replies
38
Views
2K
mintjulep
M

Part and Inventory Search

Sponsor

Back
Top