Open specific pdf in folder with vba

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,247
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,788
Office Version
  1. 2010
Platform
  1. Windows
Take this test and comment on the result.

VBA Code:
Private Sub testfiles()
  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\Acrobat Reader DC\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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,247
Office Version
  1. 2007
Platform
  1. Windows
Using the code in post number 21 I click on print & straight away this page opens.
See screenshot

If i enter say 999 in L4 of which isnt a saved file then yes i amtold file does not exist
 

Attachments

  • 3138.jpg
    3138.jpg
    124.9 KB · Views: 6

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,788
Office Version
  1. 2010
Platform
  1. Windows
Test this:

VBA Code:
Private Sub testfiles()
  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\Acrobat Reader DC\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 & sFile & " /p /h " & strFileName
  DoEvents
End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,247
Office Version
  1. 2007
Platform
  1. Windows
I have a saved file in the folder of which is 226
On the worksheet in L4 is 226
I press print and i am told Run Time Error 53 file not found.
When i debug this line is in yellow.

Rich (BB code):
Shell sPathAdobe & sFile & " /p /h " & strFileName
This is incorrect as 226 does exist in the file folder.

So now i change the invoice number on worksheet in L4 to 227
This file isnt present in the file folder.
I press print & see the msgbox as in screenshot
 

Attachments

  • 3139.jpg
    3139.jpg
    40.4 KB · Views: 7

DanteAmor

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

ADVERTISEMENT

I'm afraid it's your adobe version 😕. since I have no problems.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,247
Office Version
  1. 2007
Platform
  1. Windows
Thanks for your time with this.
My options then are two either use the original code without the input box to enter the number of copies or install Adobe Reader 11 which according to the message I seen this morning I would be downgrading as what currently is installed is more current.

I will need to think about it.
Many thanks.
 

Hasson

Board Regular
Joined
Apr 8, 2021
Messages
217
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@DanteAmor based on post#10 after write the file name in L4 and write copy number to print . does the code print the file based on copy number? if it's so . it doesn't happen with me and there is no error . if it's not , how can print based on copy number ?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,247
Office Version
  1. 2007
Platform
  1. Windows
@DanteAmor what is your thoughts about installing Adobe Reader 11

The message i received was that i was downgrading.
My thoughts on that is i mainly use Word so i only use say Adobe to open pdf files like this etc when i need to.
I mean maybe what i have installed is so powerful i dont even use it because i have no need and using version 11 will be fine ?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,788
Office Version
  1. 2010
Platform
  1. Windows
what is your thoughts about installing Adobe Reader 11
This is all about trial and error.
Try to see if it works for you, if it does not work you go back to the current version.
But if version 11 works for you and you don't need the current one, then continue with version 11.
 
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,163,844
Messages
5,833,935
Members
430,246
Latest member
Johny V

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