Open specific pdf in folder with vba

ipbr21054

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

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,173
Office Version
  1. 2007
Platform
  1. Windows
unable to find rich icon to put code inside using a mobile
You can find the code tags if you press the "three dots" menu. Select the VBA and change the text to "rich":


1638541701563.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,173
Office Version
  1. 2007
Platform
  1. Windows
I would like for the vba to look at the file number in cell L4 & open that pdf.
Do you want a new code?
I do not understand what is the relationship of the code you put and your request.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,128
Office Version
  1. 2007
Platform
  1. Windows
Hi,
For some unknown reason when i click print the invoice is printed BUT it in landscape mode & then only half of the page,left side as if i was making a card then folding it in half.

I went to the folder where the pdf invoices are saved.If i click in the pdf file to print i see an A4 sheet printed.

So i decided because i could not work out why it was doing it to then save the file, only then to have vba open that pdf so at least i had the option to print it in A4.
I was then stumped as to how i would go about printing 2 copies if needed without clicking pdf print twice.

Another code would be fine BUT should i advise a few things that the current code does or do you want to read through the code ?

Please advise before anything is done
 

ipbr21054

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

ADVERTISEMENT

You can find the code tags if you press the "three dots" menu. Select the VBA and change the text to "rich":


View attachment 52590

Ive just looked on my phone & after clicking the 3 dots Yes i do see vba along with other options BUT they are all greyed out & clicking them does nothing
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,173
Office Version
  1. 2007
Platform
  1. Windows
Ive just looked on my phone & after clicking the 3 dots Yes i do see vba along with other options BUT they are all greyed out & clicking them does nothing

But you can directly write the texts:

1638556876447.png
 

DanteAmor

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

ADVERTISEMENT

To open pdf file:

VBA Code:
Sub test()
  Dim sPath As String, strFileName As String
  sPath = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\"
  strFileName = sPath & Range("L4").Value & ".pdf"
  If Dir(strFileName) <> vbNullString Then
    ActiveWorkbook.FollowHyperlink strFileName
  End If
End Sub
 
Solution

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
4,128
Office Version
  1. 2007
Platform
  1. Windows
@DanteAmor i was just thinking.
Is it possible to have the option to print 1 or 2 copies of the pdf file in question.
If so then there is no need to open the pdf folder.

Having said that.
We need to remember that clicking print on the worksheet creates this printing issue I have.
But clicking print on the actual pdf sheet works fine.

So I’m not sure about this.
Ask if 1 or 2 copies are required then somehow apply the print option if the pdf file itself.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,173
Office Version
  1. 2007
Platform
  1. Windows
So I’m not sure about this.
Ask if 1 or 2 copies are required
Tryt this

Adjust these values with your data.
sPathAdobe = "C:\Program Files\Adobe\Reader 11.0\Reader\"
AcroRd32.exe

VBA Code:
Sub PrintPDF()
  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\Reader 11.0\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
 
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,151,960
Messages
5,767,331
Members
425,404
Latest member
Bairkus

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