Open specific pdf in folder with vba

[ipbr21054]

Hi,
Below is the code in use , unable to find rich icon to put code inside using a mobile.

The code in use is shown below.
Everything works as it should apart from 1 thing.
When the folder is opened i need to manually open the pdf file then click on print.

What i would like to do is have the code open the folder & also open the pdf in question.
The pdf in question will be saved like 225.pdf 226.pdf etc
This file name is taken from my worksheet in cell L4

Private Sub Print_Invoice_Click()
  Dim strFileName As String
  If Range("L18") = "" Then
    MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "PAYMENT TYPE WAS NOT SELECTED"
    Range("L18").Select
    Unload InvoicePrintForm
    Exit Sub
  End If
 
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
  If Dir(strFileName) <> vbNullString Then
    MsgBox "INVOICE " & Range("L4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "INVOICE NOT SAVED MESSAGE"
  Exit Sub
 
  End If
    With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    MsgBox "INVOICE " & Range("L4").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly, "INVOICE SAVED SUCCESSFULLY"
  End With
  CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\")
  Unload InvoicePrintForm
  MsgBox "ONCE PRINTED PLEASE CLICK THE OK BUTTON" & vbNewLine & vbNewLine & "TO SAVE INVOICE " & Range("L4").Value & " THEN TO CLEAR CURRENT INFO", vbExclamation + vbOKOnly, "PRINT SAVE & CLEAR MESSAGE"
      Dim i As Long, lRow As Long, ws As Worksheet
    Set ws = Application.Worksheets("DATABASE")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 6 To lRow
        If Trim(Range("G13").Value) = Trim(ws.Cells(i, 1).Value) Then

            If ws.Cells(i, 16).Value = "" Then
                ws.Cells(i, 16).Value = Range("L4").Value  ' adding invoice number to INV sheet "P"
                ActiveSheet.Hyperlinks.Add ws.Cells(i, 16), Address:="C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
                MsgBox "INVOICE " & ws.Cells(i, 16).Value & " WAS HYPERLINKED SUCCESSFULLY.", vbInformation, "HYPERLINK SUCCESSFULL MESSAGE"
            Else
                If MsgBox("COLUMN CELL P ISNT EMPTY " & ws.Cells(i, 16).Value & " IS ENTERED IN IT." & vbNewLine & "WOULD YOU LIKE TO CORRECT IT ?", vbCritical + vbYesNo, "COLUMN P NOT EMPTY MESSAGE") = vbYes Then
                    ws.Activate
                    ws.Cells(i, 16).Select
                End If
                Exit Sub
            End If
        End If
       
    Next i
       
    Range("G27:L36").ClearContents
    Range("G46:G50").ClearContents
    Range("L18").ClearContents
    Range("L4").Value = Range("L4").Value + 1
    Range("G13").ClearContents
    Range("G13").Select
    ActiveWorkbook.Save

End Sub

The invoice number is in cell L4
Currently when the folder opens I see all the saved pdf files.
I then need to manually open the pdf in question.

I would like for the vba to look at the file number in cell L4 & open that pdf.
Example.
L4 = 133
The code should then the pdf 133
Stumped as to how to achieve this.

 

[ipbr21054]

@DanteAmor I will install version 11 & see how i get on once home.

Because we had a few posts & some were for test purposes which shall i install to use.

Thanks

 

[ipbr21054]

Hi,
I have this now in use when Adobe Reader 11 is installed.
But i keep being told see screenshot
This is the best ive been able to get otherwise i was being told file does not exist

Private Sub Print_Invoice_Click()
  Dim sPath As String, strFileName As String
  Dim sPathAdobe As String, sFileExe As String
 
  sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
  strFileName = sPath & Range("L4").Value & ".pdf"
  sPathAdobe = "C:\Program Files\Adobe\Reader 11.0\Reader\"
  sFileExe = "AcroRd32.exe"
    
  If Dir(sPath, vbDirectory) = "" Then
    MsgBox "This folder does not exist: " & sPath
    Exit Sub
  End If
  If Dir(strFileName) = vbNullString Then
    MsgBox "This file does not exist: " & strFileName
    Exit Sub
  End If
    
  If Dir(sPathAdobe, vbDirectory) = "" Then
    MsgBox "This folder does not exist: " & sPathAdobe
    Exit Sub
  End If
  If Dir(sPathAdobe & sFileExe) = vbNullString Then
    MsgBox "This file does not exist: " & sPathAdobe & sFileExe
    Exit Sub
  End If
    
  Shell sPathAdobe & "AcroRd32.exe /n /t " & strFileName
  DoEvents
End Sub

 

[DanteAmor]

You can generate a new sample pdf file, and try again, but trying to open the sample file.

 

[ipbr21054]

Hi,
I tried the code where I knew the file existed. Example 227 was in the saved folder.
The worksheet in L4 was 227
I run the code to see an error message.

So I changed L4 to 228 now this file isn’t in the saved folder.
I ran the code to be told unable to locate file.

Can you share your worksheet as know I have Adobe 11 installed I can see what happens with yours.

Thanks.
It time consuming so might have to revert back to me pressing the button etc.

 

[DanteAmor]

Just open an excel file (any) save the sheet as pdf with the name, for example 222.pdf and try again.

 

[ipbr21054]

Hi,
This is not going to work so i think i must just go back to my original code in use.

When i save as pdf then select save i am told nothing to print as fil not found.
This was just saving an empty excel sheet & not anything to do with out test code

 

[DanteAmor]

but write something on the test sheet so that you can save as pdf and later print it with the maco.

 

[ipbr21054]

See attached screeenshot.
I saved the excel file as 300 pdf.
I then put 300 in worksheet cell L4

 

[ipbr21054]

In this test i opened the test file named 300 & closed it.
On the worksheet i had 300 in L4
I pressed print to see the attached screenshot.

As you can see it advises you file cannot be found.
Looking behind the message you can see the 300 file.
If i click on the 300 pdf file it opens fine.