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]

Morning,
I have the code in use as shown below.

All works fine up to the part here.
I am asked Number Of Copies
I enter 1 & click OK
I then see the msg box of which is attached to this post.
I then see another msg box also attached to this post.
It opened Adobe Acrobat Reader DC

You note above was for Adobe|Reader 11 etc but i dont see where that is other than what i have put in my code.
It looks like its looking for the file within the Adobe Reader page that opens & not in the folder path specified.

Private Sub Print_Invoice_Click()
  Dim sPath As String, strFileName As String, sPathAdobe As String
  Dim copies As Long

  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
  If Range("L18") = "" Then
    MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "PAYMENT TYPE WAS NOT SELECTED"
    Range("L18").Select
   
    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
   
  End With
 
  sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
 
  strFileName = sPath & Range("L4").Value & ".pdf"
  sPathAdobe = "C:\Program Files\Adobe\Acrobat Reader DC\Reader\"
 
  
  copies = Application.InputBox("Number of copies :", "PRINT PDF", , , , , , 1)
  If copies = 0 Then Exit Sub
  
  If Dir(strFileName) <> vbNullString Then
    For i = 1 To copies
      Shell sPathAdobe & "AcroRd32.exe /n /t " & strFileName
      DoEvents
    Next
  End If
  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 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

 

[ipbr21054]

@DanteAmor i have attached 21 screen shots for you.
One shows what i see when the pdf invoice is open from the saved folder.
The other is what i see when i run the code from the worksheet.

 

[DanteAmor]

Could you do a little test with the code from post #10

 

[ipbr21054]

Test ive done was this.
Using this code below i click print & i am asked how many copies.
I enter 1 then click ok
I then see the msg on supplied in screenshot

Private Sub Print_Invoice_Click()

  Dim sPath As String, strFileName As String, sPathAdobe As String
  Dim copies As Long, i As Long
 
  sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
  
  strFileName = sPath & Range("L4").Value & ".pdf"
  sPathAdobe = "C:\Program Files\Adobe\Acrobat Reader DC\Reader\"
   
  copies = Application.InputBox("Number of copies :", "PRINT PDF", , , , , , 1)
  If copies = 0 Then Exit Sub
   
  If Dir(strFileName) <> vbNullString Then
    For i = 1 To copies
      Shell sPathAdobe & "AcroRd32.exe /n /t " & strFileName
      DoEvents
    Next
  End If

End Sub

I have also checked the path when using this code below & i click print then 1 copy followed by msg shown as Run time error 53, screenshot attached.
When i debug this line is shown in yellow, see screenshot.

Private Sub Print_Invoice_Click()

  Dim sPath As String, strFileName As String, sPathAdobe As String
  Dim copies As Long, i As Long
 
  sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
  
  strFileName = sPath & Range("L4").Value & ".pdf"
  sPathAdobe = "C:\Program Files\Adobe\Acrobat Reader DC\Reader\AcroRd32"
   
  copies = Application.InputBox("Number of copies :", "PRINT PDF", , , , , , 1)
  If copies = 0 Then Exit Sub
   
  If Dir(strFileName) <> vbNullString Then
    For i = 1 To copies
      Shell sPathAdobe & "AcroRd32.exe /n /t " & strFileName
      DoEvents
    Next
  End If

End Sub

Only thing i noticed is mine is Adobe Acrobat Reader where yours is Adobe Reader 11 but that should be fine.
If i try & download / install Reader 11 i am told my version installed is mor current.

Ive checked the spelling, then tried with & with the exe extension in the code but i only see the 2 scenarios above every time.
Not sure now what to do as each time the above only.

Many thanks.

If this is an issue then maybe i should just use the old code you advised & manually click print when the file folder is open ?

 

[DanteAmor]

Change this line:

sPathAdobe = "C:\Program Files\Adobe\Acrobat Reader DC\Reader\AcroRd32"

For this:

sPathAdobe = "C:\Program Files\Adobe\Acrobat Reader DC\Reader\"

Displays the path and filename in an image.
In another image it shows the content of cell L4.
Note: the sheet with the file name in L4 must be active.

 

[ipbr21054]

Hi
Imight be getting confused with your last reply BUT do you mean like this now ?

Private Sub Print_Invoice_Click()

  Dim sPath As String, strFileName As String, sPathAdobe As String
  Dim copies As Long, i As Long
 
  sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
 
  strFileName = sPath & Range("L4").Value & ".pdf"
  sPathAdobe = "C:\Program Files\Adobe\Acrobat Reader DC\Reader\"
  
  copies = Application.InputBox("Number of copies :", "PRINT PDF", , , , , , 1)
  If copies = 0 Then Exit Sub
  
  If Dir(strFileName) <> vbNullString Then
    For i = 1 To copies
      Shell sPathAdobe & "AcroRd32.exe /n /t " & strFileName
      DoEvents
    Next
  End If
End Sub

If so i press print & i am asked how many copies so i enter 1 & press OK.
Then thats it.

Nothing else happens at all after clicking the ok button

Its also the same if i enter 2 then press ok.
The worksheet in question is active as in shown on the screen etc

 

[ipbr21054]

Just for some information,

I changed

If copies = 0 Then Exit Sub

To This

If copies = 1 Then
MsgBox "Hi"

THen Hi message was shown so at least we know up to that point all is ok ??

 

[ipbr21054]

If i run through the code using F8

After this line

If Dir(strFileName) <> vbNullString Then

It then jumps to End If

 

[DanteAmor]

sPathAdobe = "C:\Program Files\Adobe\Acrobat Reader DC\Reader\"

That line is correct.

Apparently the problem is in the name of the file or the path where you have the file.

What data do you have in cell L4.

And I want to see a picture of the folder:

sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"

 

[ipbr21054]

Currently L4 is showing invoice 228

When the file is saved it is saved as "in this case" 228
You can also see all the other saved invoices.

Right click on saved file & select location properties to see this

C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES