Add PDF to Button

[Legacy 436357]

Could print to PDF be added to this button?

My print area is B2:H53

Private Sub CommandButton1_Click()
   Dim NxtRw As Long
   Application.EnableEvents = False
   If Range("J3").Value = "" Then
      NxtRw = 3
   Else
      NxtRw = Range("J2").End(xlDown).Offset(1).Row
   End If
   Range("J" & NxtRw).Value = Range("H6").Value
   Range("K" & NxtRw).Value = Range("H3").Value
   Range("L" & NxtRw).Value = Format(Now(), "MM/dd/yyyy HH:MM:SS")

   Application.EnableEvents = True
End Sub

 

[ekrause]

Simple answer, yes, it can be added:

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Mr Excel Test" _ , Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

You may want to be more prescriptive with some of the options. The file name section ("Filename:=") would also include the folder path if you want to save it somewhere specifically. You can find the file path of current workbook by using a variable with "ThisWorkbook.Path". You may also want to tinker with Excel to make sure it prints to PDF as expected, this example printed the PDF over 2 pages. I'm not sure how but you can probably find a way to shrink to fit on one page.

 

[Legacy 436357]

Thanks I get error with my attempt:

Private Sub CommandButton1_Click()
   Dim NxtRw As Long
   Application.EnableEvents = False
   If Range("J3").Value = "" Then
      NxtRw = 3
   Else
      NxtRw = Range("J2").End(xlDown).Offset(1).Row
   End If
   Range("J" & NxtRw).Value = Range("H6").Value
   Range("K" & NxtRw).Value = Range("H3").Value
   Range("L" & NxtRw).Value = Format(Now(), "MM/dd/yyyy HH:MM:SS")

   Application.EnableEvents = True
   Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:Users/Desktop", _
   Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
End Sub

 

[ekrause]

2 things:
1. you are using the wrong "/" for path controls. You will need to replace it with "".
2. I would also use the following to get your PDF to be printed to the same location as your workbook.

CurrentPath = ActiveWorkbook.Path
   Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=CurrentPath & "\Workbook Name", _
   Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

To change the name of your PDF update "Workbook Name". In my example "\Workbook Name" creates a PDF Named workbook name.

Let me know if that doesn't work.

 

[Legacy 436357]

It opens Workbook Name.pdf - Adobe Acrobat Reader DC

The document is blank.

Are there settings I need to make? Also how can I specify the folder that these are saved in?

I am sorry for not being specific I wasn't sure about the details.

Oh and how is the print area set for the PDF?

 

[ekrause]

Sorry, you'll want to update the "Selection" to the range of cells you want to print. If the macro is already running on the correct worksheet you can replace "Selection.ExportAsFixedFormat" with "Range("B2:H53").ExportAsFixedFormat", otherwise you will have to navigate to the correct worksheet.

The easiest way to specify the folder the file will be saved in is to copy it directly from the file directory. Navigate to the folder you want to save your files in and copy the address from the navigation bar. This file location will be static and the files will always save in the same location, potentially saving over previous versions of the file.

 

[Legacy 436357]

It isn't liking me:

Private Sub CommandButton1_Click()
   Dim NxtRw As Long
   Application.EnableEvents = False
   If Range("J3").Value = "" Then
      NxtRw = 3
   Else
      NxtRw = Range("J2").End(xlDown).Offset(1).Row
   End If
   Range("J" & NxtRw).Value = Range("H6").Value
   Range("K" & NxtRw).Value = Range("H3").Value
   Range("L" & NxtRw).Value = Format(Now(), "MM/dd/yyyy HH:MM:SS")

   Application.EnableEvents = True
   CurrentPath = ActiveWorkbook.Path
   Selection.Range ("B2:H53") Type:=xlTypePDF, Filename:=CurrentPath & "C:\Users\xj\Desktop", _
   Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
End Sub

 

[ekrause]

Try this - Filename:= "C:\Users\xj\Desktop\Test PDF Name". VBA is having trouble locating that file path.

Let me know if that works.

 

[Legacy 436357]

The debug makes these letters red:

Private Sub CommandButton1_Click()
   Dim NxtRw As Long
   Application.EnableEvents = False
   If Range("J3").Value = "" Then
      NxtRw = 3
   Else
      NxtRw = Range("J2").End(xlDown).Offset(1).Row
   End If
   Range("J" & NxtRw).Value = Range("H6").Value
   Range("K" & NxtRw).Value = Range("H3").Value
   Range("L" & NxtRw).Value = Format(Now(), "MM/dd/yyyy HH:MM:SS")

   Application.EnableEvents = True
   CurrentPath = ActiveWorkbook.Path
   Selection.Range ("B2:H53") Type:=xlTypePDF, Filename:= "C:\Users\xj\Desktop\Test PDF Name", _
   Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
End Sub

 

[Fluff]

Try

Range("B2:H53").ExportAsFixedFormat Type:=xlTypePDF, FileName:=CurrentPath & "C:\Users\xj\Desktop", _
   Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True