Solved

open pdf in excel from cell name

Posted on 2013-10-27
6
887 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

627 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