Existing code needs an edit

ipbr21054

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

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.

Code:
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Give this a try:

Code:
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
Do you want to open in Word or just use the default application (e.g. Adobe PDF Viewer). Default application is easiest:

Code:
Private Sub CommandButton1_Click()

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

End Sub

WBD
 
Upvote 0
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.


Code:
Private Sub CommandButton1_Click()CreateObject("Shell.Application").Open "C:\Users\Ian\Desktop\REMOTES ETC\DR COPY INVOICES\" & txtInvoiceNumber.Value & ".pdf"
End Sub
 
Upvote 0
Code:
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
 
Upvote 0
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.
 
Upvote 0
Actually i used this code.

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
 
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

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
Back
Top