Good Morning Ladies & Gents,
Have posted this a few days ago in the General Thread
excel-general/1328016-link-to-open-a-pdf-file.html (Tried to post the link but I am not allowed to post any kind of link / images etc)
Unfortunately have had no responses, so I thought I would try in this sub forum.
Any hints, thoughts, guidance, magic fairy dust would be gratefully appreciated,
Tony
what is your question?
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
I found your thread and looked at your file
As far as I am aware, it is not possible to get (or follow) embedded links by formula
But here is something that you can do
1. Add a new sheet (Links) with a simple table
Assets in column A
Full path to PDF in column B
2 Use a standard lookup inside a HYPERLINK formula in sheet Test Kit List to create a usable link
=HYPERLINK(VLOOKUP(C4,Links!A:B,2,0),C4)
Hi Kev,
Thanks for responding.
I have done that previously on another project where I only had about 12 pdf's to link to but didn;t use vlookup in that case.
Unfortunately this project has around 430 plus Calibration Certificates. I was hoping to try and minimise errors with some of my users (Not the best when it comes to using excel unfortunately).
I'll give it a whirl tomorrow and see how it goes (Completely forgot about the hyperlink command) !
Thanks for the advice,
Tony
Another way to achieve what you want would be to use VBA which could use your existing links
Would you prefer that?
Morning Kev,
I'll try with the hyperlink version initially, see how I get on with that. VBA option would be nice but have only dipped my toe into VBA programming and not even at baby steps level !
Thanks,
The code for the VBA solution is short
If you want to test it out
1 Make a copy of your workbook
2 Place the code below in the SHEET code window as detailed below
- activate Test Kit List
- right-click on sheet name tab \ click View Code \ paste code into that window
Please Login or Register to view this content.
3 Close VBA editor and activate sheet Test Kit List
4 Click on any cell in column E and the code should hyperlink automatically based on asset number in column C
5 The workbook requires saving as macro-enabled
NOTE
The code is brief and opens the link if everything is there and does nothing if it isn't
It could be expanded to help you diagnose problems if preferred
Last edited by kev_; 09-25-2020 at 07:01 AM.
Hi Kev,
Unfortunately I keep getting an error "Cannot open specified file".
Will go back to the sheet I created a few years ago and try to see what difference there is (apart from the vlookup part)
T
Thanks Kev,
I didn't see that post re VBA until just now. Will give a shot and will get back to you later,
Tony
At a guess the path is incomplete ...Originally Posted by theaton56
- so investigate VLOOKUP element in isolation
Does this return the FULL path to the file, including drive and extension?
=VLOOKUP(C4,Links!A:B,2,0)
Hi Kev,
Sorry I didn't get back sooner, bit hectic at the moment. I tried the VBA on the test file and that works very nicely. However, when I put in my working file, it doesn't seem to do anything at all. I'll "play around" a bit more to see if I can fathom it out as to what is happening or what is not happening !
Tony
Hi Kev,
Sussed it ! My fault as I had retained extra columns with the same Asset number. So obviously what was happening is that as this column didn't have a hyperlink associated with it, nothing was happening. As soon as I deleted this extra column, it worked.
Many thanks for your time and patience !
All the best
Tony
glad you got there
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks