Existing code needs an edit

[ipbr21054]

Morning all,
I have a code which works well & saves as a Word doc

I would like some advice on having save as a pdf please.
I have recorded a macro of saving a pdf but unsure what i need to remove from the existing code to apply the pdf save option.

Both codes supplied below if you could assist.
Many thanks.

Current save as Word code

Private Sub Clear_Invoice_After_Printing_Click()    Dim objWord As New Word.Application
    'Copy the range Which you want to paste in a New Word Document as a screenshot
    Dim strFileName As String
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & Range("N4").Value & ".doc"
    Range("G3:O60").CopyPicture xlPrinter
        If Dir(strFileName) <> vbNullString Then
        MsgBox "INVOICE " & Range("N4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly
    Else
        'Code here to save new file
            With objWord
        With .Documents.Add
            .Parent.Selection.Paste
            .SaveAs strFileName
            .Close
        End With
        '.Visible = True
        .Quit
    End With
    MsgBox "INVOICE " & Range("N4").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly
    
    Range("G13:I18").ClearContents
    Range("N14:O18").ClearContents
    Range("G27:N42").ClearContents
    Range("G13:I13").ClearContents
    Range("G45:I49").ClearContents
    Range("N14:O16").ClearContents
    Range("L18:O18").ClearContents
    Range("G27:N42").ClearContents
    Range("N4").Value = Range("N4").Value + 1
    Worksheets("INV2").Range("N4").Value = Range("N4").Value
    Range("G13").Select
    ActiveWorkbook.Save
    End If
End Sub

Recorded Macro or you could advise otherwise.

Sub PDFTEST1()
'
' PDFTEST1 Macro
' 
'
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\Ian\Desktop\DR.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
End Sub

 

[wideboydixon]

Give this a try:

Private Sub Clear_Invoice_After_Printing_Click()
    
    Dim strFileName As String
    
    strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & Range("N4").Value & ".pdf"
    If Dir(strFileName) <> vbNullString Then
        MsgBox "INVOICE " & Range("N4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly
        Exit Sub
    End If
    
    With ActiveSheet
        .PageSetup.PrintArea = "$G$3:$O$60"
        .ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
        MsgBox "INVOICE " & Range("N4").Value & " WAS SAVED SUCCESSFULLY", vbInformation + vbOKOnly
        Range("G13:I18").ClearContents
        Range("N14:O18").ClearContents
        Range("G27:N42").ClearContents
        Range("G13:I13").ClearContents
        Range("G45:I49").ClearContents
        Range("N14:O16").ClearContents
        Range("L18:O18").ClearContents
        Range("G27:N42").ClearContents
        Range("N4").Value = Range("N4").Value + 1
        Worksheets("INV2").Range("N4").Value = Range("N4").Value
        Range("G13").Select
        ActiveWorkbook.Save
    End With

End Sub

WBD

 

[ipbr21054]

Morning WBD,
That worked.

One thing i forgot about was my command button for then opening the pdf.
Originally it was for Word doc.

Could you advise how i am to edit the working code below but to open the pdf as at present im now getting Run time error 5174 because of this.

Many thanks for your time & input.

Private Sub CommandButton1_Click()Set wordapp = CreateObject("word.Application")
wordapp.Documents.Open "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES" & "/" & txtInvoiceNumber.Value & ".doc"
wordapp.Visible = True
End Sub

 

[wideboydixon]

Do you want to open in Word or just use the default application (e.g. Adobe PDF Viewer). Default application is easiest:

Private Sub CommandButton1_Click()

CreateObject("Shell.Application").Open "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & txtInvoiceNumber.Value & ".pdf"

End Sub

WBD

 

[ipbr21054]

Many thanks,worked a treat.

Have a nice day.

 

[ipbr21054]

Morning,

Last night i had a thought of something that i did not cover.
When i press the command button it loads the invoice in question for text box named txtInvoiceNumber,at present this works fine.
Other than a number value it will show N/A so i need this code edited so if no numerical value is present them show msg box otherwise check numerical value & continue to show the pdf invoice.

Private Sub CommandButton1_Click()CreateObject("Shell.Application").Open "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & txtInvoiceNumber.Value & ".pdf"
End Sub

 

[wideboydixon]

Private Sub CommandButton1_Click()
    If IsNumeric(txtInvoiceNumber.Value) Then
        CreateObject("Shell.Application").Open "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & txtInvoiceNumber.Value & ".pdf"
    End If
End Sub

WBD

 

[ipbr21054]

Hi,
Thanks for the reply.
That code supplied is what i used as from Sunday,sorry i forgot to reply back here advising it as i was sidetracked with a ghost error of which ive now solved.

Thanks for the reply though.

 

[ipbr21054]

Actually i used this code.

Private Sub CommandButton1_Click()



    If IsNumeric(txtInvoiceNumber) Then
    
        CreateObject("Shell.Application").Open "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & txtInvoiceNumber.Value & ".pdf"
    Else
    
        MsgBox "Invoice N/A For This Customer"
    End If


End Sub