Solved

open pdf in excel from cell name

Posted on 2013-10-27
6
750 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

777 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