Link to home
Start Free TrialLog in
Avatar of Jeff McClellan
Jeff McClellanFlag for United States of America

asked on

Link excel field to find and open a PDF file with the field value in the filename.

I have an Excel table built with a data connection to a folder. Works great, updates as files are added to the folder.

One of the table columns is named "Sample number".  When selected, I want the sample number to open a corresponding PDF file.

An example of a "Sample number" would be 202106102266. When clicked, it would open the file "R00202106102265 + 202106102266  (Allred 2 Apple Fuji).pdf" in the directory C:\Users\jtmcc\NCC Results\PDF files\.

Likewise, clicking sample number 202106102265 in the workbook would open the same file.

What's the best way do that? Macro or formula? Can you provide an example please?
Avatar of Steve Knight
Steve Knight
Flag of United Kingdom of Great Britain and Northern Ireland image

You could use VBA macro linked to a double click event on a call for instance or put a hyperlink in the cell to file://c:/users/etc.

Can you provide a sample sheet of a few lines of your data please - leave out anything that matters of course.

Steve
Avatar of Jeff McClellan

ASKER

Here is an example of what the Excel workbook might look like, and a folder of PDF files that would be associated with the "Sample number" in the workbook.

Thank you!!!!

XX_example.xlsx
XX_PDF.zip
OK so you don't actually have fields in the excel sheet that give you all the components of the filename of the PDF then?

In which case we are going to have to search for a file called "R00" & B2& "*.pdf" which can be done in VBA and then launched.

Is there likely / possibly going to be more than one file in the directory that starts with that sample number, what do you want to do then?

Does this find the right files for you?

XX_example.xlsm


Steve
I get this error. Not sure how to update declare as advisedUser generated image

Sorry that wasn't being used, can remove that.  If it is finding the right PDF we can then see what way to launch it on your PC

Alt F11 into VBA Editor and you can remove that whole line

This was version I thought I had posted.

XX_example.xlsm
I can run the file (and it works beautifully by the way ;-) thank you very much) if I download your xlsm example to my desktop and have the PDF folder on the desktop with the reports in it.

But if I copy both (xlsm and PDF foleder) to another folder, open the xlsm and double click "Sample number", the sub doesn't run. I end up actually selecting the cell for editing.
Not sure if this matters, but the problem folder - "Master Tables" - is a One Drive folder.


C:\Users\jtm\OneDrive\Master Tables
OK, sounds like you aren't running macros in it maybe?  Can you get into VBA editor Alt-F11?

Onedrive shouldn't make any difference, it is looking in a PDF subfolder at the moment of where the spreadsheet is - are they in the same place?

As to the filenames of the PDF - so can you define exactly how to find the right PDF file then - i.e. how you would find the right file manually?

It seems we might have to do a lot of guessing, e.g.

R00 + Sample number*.pdf
or presumably if not found then
R00*SampleNumber*.pdf?

What about one like:

R00202106102003 + 202106102008

Is that only for ones ending in 2003 and 2008 or are we supposed to be finding it for 2004,2005,2006,2007 too?

Steve
I find it manually by looking for the sample number somewhere in the filename. Sample numbers are always unique.
ASKER CERTIFIED SOLUTION
Avatar of Steve Knight
Steve Knight
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Glad it helped!
I tested this with "myFolder" (file and reference PDF folder) saved on my desktop, and it ran perfectly.

When I moved the "myFolder" (file and ref. PDF folder) to a different location, it produces an error:
User generated image
How can I make this work regardless of where "myFolder" is saved?  I need to make this available to others so they can access and use it from a shared OneDrive folder.
It should not matter or care where it is. When it fails what error does it give?
If you press Control G for the immediate window in VBA editor (Alt F11) and when it has errored put in:

print strSample
print strFiles
print myFolder

myFolder should be PDF sub folder under the location where the XLSM is stored
strSample should be the filename it is lookup up
strFiles is the file it has found.

I imagine your "myFolder" is being set wrongly?


If you are launching it from a web browser onedrive it most likely won't work though - needs to be a locally synced drive.


The code from the last version was this too, I had missed the cancel out in my copy/paste before which stops it then trying to being up the right-click or double click action on the cell.

Steve

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)


' Only allow double clicking on one call and in column 2
If Target.Cells.Count > 1 Then Exit Sub
If Target.Cells.Column <> 2 Then Exit Sub
   
    Dim myFolder As String
    myFolder = ActiveWorkbook.Path & "\PDF"  ' Set path for folder - by default should pickup current folder plus PDF


    Dim objShell As Object: Set objShell = CreateObject("WScript.Shell")


    Dim strSample As String
    Dim strFiles As String
    strSample = myFolder & "\" & "R00*" & Target.Cells.Value & "*.pdf"
   
    strFiles = Dir(strSample)
   
    If strFiles <> "" Then
        If MsgBox("Do you wan to open this file?" & Chr(10) & Chr(10) & strFiles, vbYesNo + vbInformation) = vbYes Then CreateObject("Shell.Application").ShellExecute myFolder & "\" & strFiles
    Else
        MsgBox "No PDF file found matching " & strSample, vbOKOnly + vbExclamation
    End If
    Cancel = True


End Sub




Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


' Only allow double clicking on one call and in column 2
If Target.Cells.Count > 1 Then Exit Sub
If Target.Cells.Column <> 2 Then Exit Sub
   
    Dim myFolder As String
    myFolder = ActiveWorkbook.Path & "\PDF"  ' Set path for folder - by default should pickup current folder plus PDF


    Dim objShell As Object: Set objShell = CreateObject("WScript.Shell")


    Dim strSample As String
    Dim strFiles As String
    strSample = myFolder & "\" & "R00*" & Target.Cells.Value & "*.pdf"
   
    strFiles = Dir(strSample)
   
    If strFiles <> "" Then
        If MsgBox("Do you wan to open this file?" & Chr(10) & Chr(10) & strFiles, vbYesNo + vbInformation) = vbYes Then CreateObject("Shell.Application").ShellExecute myFolder & "\" & strFiles
    Else
        MsgBox "No PDF file found matching " & strSample, vbOKOnly + vbExclamation
    End If
    Cancel = True


End Sub

Open in new window

I copied your most recent code to the xlsm on my desktop, and with the PDF folder on the desktop, both the DoubleClick and RightClick subs run as expected.

But if I copy the xlsm file and the PDF folder to a folder like "C:\Master Tables", open the xlsm file and double click or right click a "Sample number", the subs don't run at all.   I just end up selecting the "Sample number" cell in edit mode or open the context menu.  I don't get any error, the code just doesn't run at all.

I've also copied the xlsm and PDF to other folder locations with the same effect. It seems the only place it works is when I open the xlsm file from the Desktop?
Have a look at the file in explorer right click and properties and see if it said the file is locked?

Are any macros running in excel from that folder - do you get asked to enable macros?


The file is not locked.  There aren't any macros running, and I dont get asked to enable macros.
User generated image
So when you open from that folder and go into vba editor can you see the macro,  can you run from immediate window anything?
That was it.  Turns out Excel Trust Center was set to disable macros without notification!  So no go on the copied files.

Thanks for hanging in there with me.  
That's good,  always bit if s pain and just need to explain to other 
Now I get the runtime error 52 - bad filename or number 
fails at

 strFiles = Dir(strSample)
Please try content of variables from comment above... 3 print statement,  using phone at moment so not typing again and won't allow copy paste 


typed in 3 print statements in immediate window.  hit enter after each.  get a blank line.
Looks like it fails with print strFiles - returns a blank row
Guessing Dir(strSample) is where the problem is?

Seems that it is only failing with the xlsm file saved in my OneDrive directory.

Works from the desktop.
Works from C:\Master Tables

Doesn't work for the path returned for OneDrive folder.  I think because the macro returns a URL for strSample rather than a path on my hard drive??
https://nrvv-my.sharepoint.com/personal/jeff_nittanyrv_com/Documents/Master Tables\PDF\
I'll try it from a onedrive folder later. is it actually opened from c drive or Web page?
I'm open to suggestions on how to best share this file with colleagues.  

I was hoping I could put the xlsm file and PDF folder in a shared OneDrive folder, then share it with colleagues to use the xlsm file to do.  

Our xlsm file (with your macros) are built with a data connection to all xlsx files stored in a different folder on OneDrive that refreshes on opening.  The idea was that the xlsm file would update itself with current information on opening, and then the users of the xlsm file would be able to click on "Sample number" to pull up their reports.  
Hmm,

I'd never tried getting the path before for a workbook from VBA stored on Onedrive but clearly MS have surpassed themselves and broken existing function and return a URL rather than local path for anything on onedrive - grreat really, means you have to handle things differently if somebody moves it from A to B.  Thanks!

https://social.msdn.microsoft.com/Forums/office/en-US/1331519b-1dd1-4aa0-8f4f-0453e1647f57/how-to-get-physical-path-instead-of-url-onedrive?forum=officegeneral
http://www.vbaexpress.com/kb/getarticle.php?kb_id=1217

We could go through rigmarole above to get the path, will see if better way.  If it wasn't for the searching it would be trivial to put a hyperlink in that launched direct to the onedrive URL but you don't know what the filename is

Where are the PDF files - are they always in the same location or just in Onedrive wherever that gets synced to again?  Only reason for all this is so filenames could be looked up using a folder search.

Steve
Agreed!  It's quite frustrating how Microsoft asks you to use their tech, but builds it so it doesn't really work well together.
Not sure I understand your question. The excel file is named "Master Completed Samples.xlsm".

I can save all of the files including the PDF folder in a dedicated folder - always in the same place - in OneDrive.