Solved

open pdf in excel from cell name

Posted on 2013-10-27
6
785 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
  • 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 500 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

821 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