VBA - Open PDF Files based on partial data string

bump

You can fire some code when selectig a cell and I have done that below. Sometimes this can become annoying and you may want to change to only firing the code if the cell is double-clicked.

If you want to do that, remove the very first line of my code and replace it with the second line that I currently have commented and also uncomment the "Cancel = True" line.

Some assumptions (code should be able to be adapted if any of these are incorrect)..

- Your pdf file names are in column A only and below row 1.

- The names are all numeric and entered as numbers but formatted to show 5 digits.

- Following from the above point, the names in the cells do not contain the ".pdf" extension

- All the files in the folder start with "XXX-B-CCC"

- You said you didn't want to use hyperlinks but I have taken that to mean on the sheet itself, I have used a hyperlink in my code.

I suggest that you test this in a copy of your workbook.

Note that you will have to change the path back to suit your location.

<font face=Courier New><br><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">'Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel <SPAN style="color:#00007F">As</SPAN> Boolean)</SPAN><br><br>    <SPAN style="color:#00007F">Dim</SPAN> fName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> fPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> fullName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#007F00">'Column with pdf file names, change to suit</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> pdfcol As <SPAN style="color:#00007F">String</SPAN> = "A"<br>    <br>    <SPAN style="color:#007F00">' Bail out if something is obviously wrong</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Target.Column <> Columns(pdfcol).Column <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Target.Row < 2 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> IsEmpty(Target) <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsNumeric(Target.Value) <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br><SPAN style="color:#007F00">'    Cancel = True 'Uncomment if using DoubleClick event</SPAN><br>    <br>    <SPAN style="color:#007F00">'set path to file: CHANGE TO SUIT</SPAN><br>    fPath = "C:\Test Folder\"<br>    <br>    <SPAN style="color:#007F00">'get file name from cell & adjust for formatting</SPAN><br>    fName = ActiveCell.Value<br>    fName = Right("0000" & fName, 5)<br>    <br>    <SPAN style="color:#007F00">'add the extra 'junk'</SPAN><br>    fName = "XXX-B-CCC" & "*" & fName & "*" & ".pdf"<br>    <br>    <SPAN style="color:#007F00">'try to get the full path and name from the folder</SPAN><br>    fullName = fPath & Dir(fPath & fName)<br>    <br>    <SPAN style="color:#00007F">If</SPAN> fullName = fPath <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#007F00">'file not found message</SPAN><br>        MsgBox "No such filename as " & fName & vbCrLf & _<br>            "In Path " & fPath, vbExclamation<br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#007F00">'open it</SPAN><br>        ActiveWorkbook.FollowHyperlink fullName<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Peter,

This works perfectly well. Many thanks for your help.

I have two observations that may help other users :

1- If the file name contains the symbol "#" then the 'link' will not work.

2- There is a delay when opening the files from a network drive (can be up to 10-15 seconds) . This may come from my system, but I think that having the code to search the filename with wildcards may play a part in that. Access through "regular" hyperlinking is significantly faster.

Regards

--
opazzo

opazzo said:

I have two observations that may help other users :

1- If the file name contains the symbol "#" then the 'link' will not work.

2- There is a delay when opening the files from a network drive (can be up to 10-15 seconds) . This may come from my system, but I think that having the code to search the filename with wildcards may play a part in that. Access through "regular" hyperlinking is significantly faster.

Click to expand...

1. That is also true of a "regular" hyperlink isn't it?

2. Absolutely true and not unexpected - linking directly to a file rather than searching for it first should be quicker.