Open a PDF doc from Excel dash board

[IannW]

Hi All

Note to self, write your post in word and then copy to thiswindow, as I have now written the post 3 times, only for the window to crashand reopen without my text L.

Now to my question….
I have created a dash board using Excel and want to be ableto link to PDF docs in another folder. My PDF docs are all name as 4 numbers, ie, 0017.pdf, 0019.pdf and0276.pdf etc…
I want to be able to enter a 4 digit number into a cell andthen click a button next to it that then opens the PDF doc of the same name.
I have tried using a data validation cell using datanarrowed down using Power Query to remove the file extension but don’t know howto then open the selected file, and I didn’t really want to have to scrollthrough a list of around 500 numbers.
Any ideas gratefully appreciated.

IannW

 

[Domenic]

One way would be to use the FollowHyperlink method of the Workbook object...

Sub OpenPDF_1()

    Dim strPath As String
    Dim strFileName As String
    
    'Set the Path to the folder (change accordingly)
    strPath = "C:\Users\Domenic\Documents\MyFolder\"
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    
    'Get the PDF filename (change the sheet name and cell reference for the four digit filename accordingly)
    strFileName = Worksheets("Sheet1").Range("A2").Value & ".pdf"
    
    'Open PDF file, if it exists
    If Len(Dir(strPath & strFileName)) > 0 Then
        ActiveWorkbook.FollowHyperlink strPath & strFileName
    Else
        MsgBox "File not found!", vbExclamation
    End If
    
End Sub

Another way would be to use the Shell function...

Sub OpenPDF_2()

    Dim strProgramPathAndName As String
    Dim strPathToFolder As String
    Dim strFileName As String
    Dim TaskID As Double
    
    'Change the path and program name accordingly
    strProgramPathAndName = "C:\Program Files\Adobe\Acrobat 7.0\Reader\AcroRd32.exe"
    
    'Change the path to your folder accordingly
    strPathToFolder = "C:\Users\Domenic\Documents\MyFolder\"
    If Right(strPathToFolder, 1) <> "\" Then
        strPathToFolder = strPathToFolder & "\"
    End If
    
    'Change the sheet name and cell reference containing your four digit filename
    strFileName = Worksheets("Sheet1").Range("A2").Value & ".pdf"
    
    'Check whether PDF file exists
    If Len(Dir(strPathToFolder & strFileName)) = 0 Then
        MsgBox "File not found!", vbExclamation
        Exit Sub
    End If
    
    'Open the PDF file
    On Error Resume Next
    TaskID = Shell(strProgramPathAndName & " " & strPathToFolder & strFileName, vbNormalFocus)
    If Err <> 0 Then
        MsgBox "Unable to open PDF!", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    
End Sub

Hope this helps!

 

[IannW]

Hi

Many thanks for taking the time to reply. Another dumb question before I start, wheredo I put this code? I created an activex button but when I opened it I got this:
Private Sub CommandButton1_Click()
End Sub
I’m guessing this is not what I use. I am kinda new to this and still learning. I can figure out where I change your code,just need to know where I put it.

Many Thanks




[QUOTE=Domenic;5131202]One way would be to use the FollowHyperlink method of the Workbook object...

Sub OpenPDF_1()

    Dim strPath As String
    Dim strFileName As String
    
    'Set the Path to the folder (change accordingly)
    strPath = "C:\Users\Domenic\Documents\MyFolder\"
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    
    'Get the PDF filename (change the sheet name and cell reference for the four digit filename accordingly)
    strFileName = Worksheets("Sheet1").Range("A2").Value & ".pdf"
    
    'Open PDF file, if it exists
    If Len(Dir(strPath & strFileName)) > 0 Then
        ActiveWorkbook.FollowHyperlink strPath & strFileName
    Else
        MsgBox "File not found!", vbExclamation
    End If
    
End Sub

Another way would be to use the Shell function...

Sub OpenPDF_2()

    Dim strProgramPathAndName As String
    Dim strPathToFolder As String
    Dim strFileName As String
    Dim TaskID As Double
    
    'Change the path and program name accordingly
    strProgramPathAndName = "C:\Program Files\Adobe\Acrobat 7.0\Reader\AcroRd32.exe"
    
    'Change the path to your folder accordingly
    strPathToFolder = "C:\Users\Domenic\Documents\MyFolder\"
    If Right(strPathToFolder, 1) <> "\" Then
        strPathToFolder = strPathToFolder & "\"
    End If
    
    'Change the sheet name and cell reference containing your four digit filename
    strFileName = Worksheets("Sheet1").Range("A2").Value & ".pdf"
    
    'Check whether PDF file exists
    If Len(Dir(strPathToFolder & strFileName)) = 0 Then
        MsgBox "File not found!", vbExclamation
        Exit Sub
    End If
    
    'Open the PDF file
    On Error Resume Next
    TaskID = Shell(strProgramPathAndName & " " & strPathToFolder & strFileName, vbNormalFocus)
    If Err <> 0 Then
        MsgBox "Unable to open PDF!", vbExclamation
        Exit Sub
    End If
    On Error GoTo 0
    
End Sub

Hope this helps![/QUOTE]

 

[Domenic]

Unless you have some reason for using an ActiveX commandbutton, I would use a Form control button. First, copy the code into the code module for the sheet. You can do this by right-clicking on the sheet tab, selecting View Code, and copy/pasting the code into the code module. Then, on your worksheet, insert a Form control button by first selecting the Developer tab on the Ribbon, and then selecting Insert under the Controls group, and then Button under Form Controls. Now add it to your worksheet where you desire by clicking on a cell location. Once you do this the Assign Macro dialog box should pop up. Then select the macro from the list, and click OK. One last thing. Then mark the Sub as Private so that it can only be accessed via the button by changing this line...

Sub OpenPDF_1()

to

Private Sub OpenPDF_1()

If you're going to use the other macro, mark it as Private also.

Let me know if you have any other questions.

 

[IannW]

Hi Domenic

May thanks for taking the time to help. I have done as advised but on clicking the button, am getting the error "File Not Found" . I tried both methods on different sheets. See code below:

Private Sub OpenPDF_1()

Dim strPath As String
Dim strFileName As String

strPath = "C:\Users\Iann\Desktop\PDFTEST"
If Right(strPath, 1) <> "" Then
strPath = strPath & ""
End If

strFileName = Worksheets("Sheet1").Range("D7").Value & ".pdf"

If Len(Dir(strPath & strFileName)) > 0 Then
ActiveWorkbook.FollowHyperlink strPath & strFileName
Else
MsgBox "File not found!", vbExclamation
End If

End Sub


And the following:

Private Sub OpenPDF_2()
Dim strProgramPathAndName As String
Dim strPathToFolder As String
Dim strFileName As String
Dim TaskID As Double

'Change the path and program name accordingly
strProgramPathAndName = "C:\Users\Iann\Desktop\AcroRd32.exe"

'Change the path to your folder accordingly
strPathToFolder = "C:\Users\Iann\Desktop\PDFTEST"
If Right(strPathToFolder, 1) <> "" Then
strPathToFolder = strPathToFolder & ""
End If

'Change the sheet name and cell reference containing your four digit filename
strFileName = Worksheets("Sheet2").Range("D7").Value & ".pdf"

'Check whether PDF file exists
If Len(Dir(strPathToFolder & strFileName)) = 0 Then
MsgBox "File not found!", vbExclamation
Exit Sub
End If

'Open the PDF file
On Error Resume Next
TaskID = Shell(strProgramPathAndName & " " & strPathToFolder & strFileName, vbNormalFocus)
If Err <> 0 Then
MsgBox "Unable to open PDF!", vbExclamation
Exit Sub
End If
On Error GoTo 0

End Sub


Any Ideas?????


Many Thanks

Unless you have some reason for using an ActiveX commandbutton, I would use a Form control button. First, copy the code into the code module for the sheet. You can do this by right-clicking on the sheet tab, selecting View Code, and copy/pasting the code into the code module. Then, on your worksheet, insert a Form control button by first selecting the Developer tab on the Ribbon, and then selecting Insert under the Controls group, and then Button under Form Controls. Now add it to your worksheet where you desire by clicking on a cell location. Once you do this the Assign Macro dialog box should pop up. Then select the macro from the list, and click OK. One last thing. Then mark the Sub as Private so that it can only be accessed via the button by changing this line...

Sub OpenPDF_1()

to

Private Sub OpenPDF_1()

If you're going to use the other macro, mark it as Private also.

Let me know if you have any other questions.

 

[Domenic]

That's because in both of your examples the backslash (\) isn't being added to the path. So, for example, for the first one, you should have the following code...

[COLOR=#333333]strPath = "C:\Users\Iann\Desktop\PDFTEST[B]\[/B]"[/COLOR]
[COLOR=#333333]If Right(strPath, 1) <> "[B]\[/B]" Then[/COLOR]
[COLOR=#333333]     strPath = strPath & "[B]\[/B]"
End If
[/COLOR]

Note that the If statement is there to ensure that the backslash is added to the end of the specified path, if one hasn't already been added. But you changed it in your code, hence the problem.

Also, for your second one, you 'll find Acrobat Reader somewhere in "C:\Program Files". So you'll need to find exactly where it's located and how it's named, and then change the path and program name accordingly.

 

[IannW]

Hi Domenic

Many thanks for your quick response.

I've looked at the code and I did have the back slashes in place. I can't understand why they disappeared when I copied the code in here? Here's the code again:

Private Sub OpenPDF_1()



Dim strPath As String
Dim strFileName As String

strPath = "C:\Users\Iann\Desktop\PDFTEST"
If Right(strPath, 1) <> "" Then
strPath = strPath & ""
End If

strFileName = Worksheets("Sheet1").Range("D7").Value & ".pdf"

If Len(Dir(strPath & strFileName)) > 0 Then
ActiveWorkbook.FollowHyperlink strPath & strFileName
Else
MsgBox "File not found!", vbExclamation
End If

End Sub

On the other one, I copied the adobe exe to my desktop to ensure the path was correct.
It wouldn't have anything to do with the "strFileName = Worksheets" line?


Regards

Iann

That's because in both of your examples the backslash (\) isn't being added to the path. So, for example, for the first one, you should have the following code...

[COLOR=#333333]strPath = "C:\Users\Iann\Desktop\PDFTEST[B]\[/B]"[/COLOR]
[COLOR=#333333]If Right(strPath, 1) <> "[B]\[/B]" Then[/COLOR]
[COLOR=#333333]     strPath = strPath & "[B]\[/B]"
End If
[/COLOR]

Note that the If statement is there to ensure that the backslash is added to the end of the specified path, if one hasn't already been added. But you changed it in your code, hence the problem.

Also, for your second one, you 'll find Acrobat Reader somewhere in "C:\Program Files". So you'll need to find exactly where it's located and how it's named, and then change the path and program name accordingly.

 

[Domenic]

I just re-read your original post and I see that the .pdf extension is already added to the file name specified in D2. In that case, replace...

[COLOR=#333333]strFileName = Worksheets("Sheet1").Range("D7").Value & ".pdf"[/COLOR]

with

[COLOR=#333333]strFileName = Worksheets("Sheet1").Range("D7").Value[/COLOR]

By the way, in future, please wrap your code within code tags. It will not only make it easier to read, but it will avoid such things as backslashes going missing.

 

[IannW]

Hi Domenic

I have another question regarding a formula. I have a job schedule and I have (with help)constructed a way of filtering a persons initials to just show the jobs he isdoing. But sometimes, there may be morethan one person on the job (eg Cell AI 4), so I want the Helper2 column (formulabelow) to list the other peoples initials as well as the one I selected from mydrop down list (AQ 30). How do I tweakthe formula to include jobs with more than one set of initials as in AI 4? In other words, if there is a shared job, itwill appear on each persons search.
I have also added a link to a screen grab of theworkbook. I can’t add it here as a fileor jpg so have added it to a page on my website (scroll to bottom of page).
http://www.discoverpissouri.com/Thankyou.html
=IF(OR(AI4=$AQ$30,$AQ$30="AllEngineers"),AK4,"")
Many thanks
Iann

 

[IannW]

Hi Again

I got an email saying you had responded to my last question but I can't see anything.