Open specific pdf in folder with vba

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,248
Office Version
  1. 2007
Platform
  1. Windows
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

VBA Code:
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.
 
Last edited by a moderator:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,248
Office Version
  1. 2007
Platform
  1. Windows
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.

Rich (BB code):
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
 

Attachments

  • 3112.jpg
    3112.jpg
    28.6 KB · Views: 5
  • 3113.jpg
    3113.jpg
    29.7 KB · Views: 4
Last edited:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,248
Office Version
  1. 2007
Platform
  1. Windows
@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.
 

Attachments

  • 3116.jpg
    3116.jpg
    121.4 KB · Views: 7
  • 3117.jpg
    3117.jpg
    170.8 KB · Views: 6

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,796
Office Version
  1. 2010
Platform
  1. Windows
Could you do a little test with the code from post #10
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,248
Office Version
  1. 2007
Platform
  1. Windows
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

Rich (BB code):
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.


Rich (BB code):
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 ?
 

Attachments

  • 3130.jpg
    3130.jpg
    102.2 KB · Views: 6
  • 3132.jpg
    3132.jpg
    190.4 KB · Views: 5
  • 3133.jpg
    3133.jpg
    24.7 KB · Views: 6
  • 3134.jpg
    3134.jpg
    100.2 KB · Views: 4

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,796
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 16, 2010
Messages
4,248
Office Version
  1. 2007
Platform
  1. Windows
Hi
Imight be getting confused with your last reply BUT do you mean like this now ?

Rich (BB code):
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
 
Last edited:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,248
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Just for some information,

I changed
Rich (BB code):
If copies = 0 Then Exit Sub

To This
Rich (BB code):
If copies = 1 Then
MsgBox "Hi"

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

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,248
Office Version
  1. 2007
Platform
  1. Windows
If i run through the code using F8

After this line
Rich (BB code):
If Dir(strFileName) <> vbNullString Then

It then jumps to End If
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,796
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
4,248
Office Version
  1. 2007
Platform
  1. Windows
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
 

Attachments

  • 3135.jpg
    3135.jpg
    253.4 KB · Views: 6
  • 3137.jpg
    3137.jpg
    158.7 KB · Views: 7
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,316
Messages
5,836,600
Members
430,441
Latest member
SurendraTantia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top