Solved

open pdf in excel from cell name

Posted on 2013-10-27
6
836 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 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

751 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