Save as pdf with cell values and Print Excel file VBA

[Roger Hanson]

I am trying to create/find a VBA script to have a single button that will save the file as a pdf with cell B3 and B4 values and a date code as the file name and then print the second tab of the worksheet but I am having problems. The file prints out to a network printer without issue but when I try to open the saved pdf, it gives me an error stating that the file is not a supported file type or the file has been damaged. I suspect that the file is not really saving as a pdf. Can someone help me troubleshoot this code? I did not create the code, but found it online (probably in this forum somewhere). I am a Mechanical Engineer and coding is not among my strong skills.

Sub CommandButton21_Click()
resp = MsgBox("Complete Test?", vbYesNo)
If resp = vbYes Then
Dim sFile As String
Dim uname
sFile = "" & Format(Now(), "mm.dd.yy") & ".pdf"
uname = Range("B3") & Range("B4").Value
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:="C:\Users\roghanson\My Documents\Inspection Programs\Testing\" & uname & " " & sFile
End If
Sheets("Summary").PrintOut
End Sub

 

[Deepak]

Pls check this..

This only works if u have xl>=2007 SP2.

for office 2007 with <SP2 , you need to install a add in form below url.
http://goo.gl/FTOSrx

Option Explicit

Sub CommandButton21_Click()
Dim resp As Variant
Dim sFile As String, Mypath As String

resp = MsgBox("Complete Test?", vbYesNo)
If resp <> vbYes Then Exit Sub

sFile = Range("B3").Value & Range("B4").Value & " " & Format(Now(), "mm.dd.yy") & ".pdf"
Mypath = "C:\Users\roghanson\My Documents\Inspection Programs\Testing\"
ActiveWorkbook.Save

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Mypath & sFile, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
Sheets("Summary").PrintOut
End Sub

 

[Roger Hanson]

Thank you so much Deepak! Your code worked perfectly! I will be back for more VBA fun as this minor project is just the first of many for my department.
Thank you!