General Question

critter1982's avatar

Microsoft Excel Question?

Asked by critter1982 (4120points) February 24th, 2009

Here is my dilemma. Keep in mind I am not that computer savvy. The company I work for utilizes Microsoft Excel to keep track of all their CAD drawings. When a drawing is complete it goes into a shared area (just a shared folder) under its part number. So if a part is just released an example of the part number would be 123456.1.pdf. They are saved as .pdf’s hence the .pdf, the .1 means revision 1, and the 123456 is the part number. The excel document then contains this part number and the description and a hyperlink to this .pdf document. Here’s where the problem comes. When drawings get updated so for this example we would have 123456.2.pdf, they get placed into the same shared folder, the old revision gets moved and the hyperlink gets changed. The problem I have is that I do a ton of these and having to change the hyperlink from a .1.pdf to a .2.pdf becomes cumbersome. Is there any way in excel that I can use a macro or something else where the hyperlink would read the latest revision. So if both .1.pdf and .2.pdf were in the same directory it would pull up the .2.pdf when you clicked on the hyperlink? Thanks for your help.

Observing members: 0 Composing members: 0

5 Answers

Zaku's avatar

Not that I know of. However, what I would do instead is link to the folder, without the file name, since you are updating the folder contents. Then the link will always go to a view of what’s in the folder. (It is one more step for users when following the link, though.)

Zaku (30354points)“Great Answer” (0points) Flag as…
fireside's avatar

I suppose you could try this method, but if you are a beginner at Excel, Vusyal Basic may take more time to figure out than it is worth.

fireside (12359points)“Great Answer” (0points) Flag as…
srmorgan's avatar

visit www.mrexcel.com,
join the message board, it is free, and post.

see what kind of assistance you get.

SRM

srmorgan (6773points)“Great Answer” (1points) Flag as…
Grisson's avatar

You could version so that the current version remains consistent.
Name the old version with a date such as 1234567.200902251635.pdf

Grisson (4634points)“Great Answer” (0points) Flag as…
CyanoticWasp's avatar

There is a hyperlink “formula” in Excel. So if you have a cell, such as A10, containing the new drawing number: 123456.2.pdf.

Next, you’ll need a value for the path to that file (this might be a constant, if all of the files are in the same directory / folder), such as B10:
\\WINFILE02\DATA\PB\FieldOps\Shared\UserName\

Then concatenate those names in C10:
= b1 & a10

And the hyperlink formula in D10 (so that D10 would be a hyperlink cell created from pre-existing data, and which you don’t have to manually edit):
=hyperlink( c10, a10)

I’ve broken this out into more steps than you really need. Some of these can be combined, but this progression makes it pretty elementary.

This way the “friendly name” would be only the filename in A10.

CyanoticWasp (20271points)“Great Answer” (0points) Flag as…

Answer this question

Login

or

Join

to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
or
Knowledge Networking @ Fluther