Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

open pdf in excel from cell name

Posted on 2013-10-27
6
Medium Priority
?
970 Views
Last Modified: 2013-10-31
I have 3 columns:
File Description                                                                     File size      File name
SUZUKI-ADRESS-AE 50-E71-SPARE PARTS CATALOGUE-ENG      0.88 MB      52661b13b8094
SUZUKI-ADRESS-WIRING DIAGRAM-ENG                             0.04 MB      52660ba7a07b7


What i would like to do is click on the description and its pdf open

ex. click this C3 (start of rows) and E3 would open  52661b13b8094.pdf

the extension of the filename is not in the cell.

all pdfs are in same file location

not sure how to do this in excel vba.
Suzuki-File-reference.xlsx
0
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:netmaster1355
ID: 39603668
do it by:
=HYPERLINK("file:///c:\location\"&E3&".pdf")

replace location with your path. E3 is cell which contain your file name.
you can use it in all cells with their corresponding filename.

i hope it is usefull for you. but anyway if you insist on using VBA code to do this, please let me know.
0
 
LVL 16

Accepted Solution

by:
terencino earned 2000 total points
ID: 39603687
Hi Peter here is an update to your file, now containing some worksheet event code as follows, just substitute the correct full path in place of type_in_the_full_path_here. It uses a double-click instead of a click. This is because the only other event that traps a single click also traps tabs and arrows etc. No modification of your existing worksheet is required other than to add the worksheet event and change the extension to XLSM as per attached file

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 3 And Target.Value <> "" Then
    retval = MsgBox("Open " & Target.Offset(0, 2).Value & ".pdf for " & vbCrLf & Target.Value, vbOKCancel, "Open file?")
    If retval = vbOK Then
        ThisWorkbook.FollowHyperlink "type_in_the_full_path_here\" & Target.Offset(0, 2).Value & ".pdf"
        Cancel = True 'Prevents edit mode
    End If
End If
End Sub

Open in new window

Hope this works for you, let me know if any tweaking required
...Terry
Suzuki-File-reference.xlsm
0
 

Author Comment

by:PeterBaileyUk
ID: 39603927
how do i turn the warnings off do.cmd.setwarnings=false?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 16

Expert Comment

by:terencino
ID: 39604639
Thanks Peter, what warnings need to be turned off?
0
 

Author Comment

by:PeterBaileyUk
ID: 39613483
I think they are office ones I will leave that it doesnt matter. Is it possible to open the file in Abbyy finereader instead so I can grab the data?
0
 
LVL 16

Expert Comment

by:terencino
ID: 39613606
Yes there are a couple of options:
1. Set Abbyy as the default PDF application, this will open all your PDF files
2. Use Shell and replace the FollowHyperlinks line with this (updating the paths of course!)
Shell "full_path_to_ABBYY_Executable.exe full_path_to_PDF\" & Target.Offset(0, 2).Value & ".pdf", vbNormalFocus

Open in new window


...Terry
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

719 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