print and name PDF file

[Eddie]

I have the following code that places the serial number (pulled from the file name) in a cell and then prints a selection of a test report in PDF.

[VBA]Sub PrintSelection()
'creates strings to be set
Dim SerialDate As String
Dim SerialNum As String
Dim FullFileName As String

'sets the FullFileName variable value to the name of the file
FullFileName = ActiveWorkbook.Name
'takes the first four numbers from the FullFileName string and assigns them to the SerialDate string
SerialDate = Mid(FullFileName, 1, 4)
'takes the next four numbers from the FullFileName string and assigns them to the SerialNum string
SerialNum = Mid(FullFileName, 5, 4)

'Selects cell H8 and then enters the serial number based off of the file name in the format "SN: ####-####"
Range("H8").Select
ActiveCell.FormulaR1C1 = "SN:" & SerialDate & "-" & SerialNum

'Selects the cells that are to be printed
Range("A1:L107").Select

'Prints selection to PDF printer
Application.ActivePrinter = "Adobe PDF on Ne04:"
ActiveWindow.Selection.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne04:", Collate:=True

Pause (1.5)

SendKeys SerialDate & "-" & SerialNum, True

End Sub
[/VBA]

[VBA]Public Sub Pause(NbSec As Single)
Dim Finish As Single
Finish = Timer + NbSec
DoEvents
Do Until Timer >= Finish
Loop
End Sub
[/VBA]

I cannot get the code to name the file automatically once the save screen comes up. The code in question is bolded and underlined. Is it possible to have the file named automatically? Thanks!

[Oorang]

I dislike the print driver. It's not fully VBA compatabile. You can use [vba]ActiveSheet.PrintOut ActivePrinter:="Adobe PDF on NE04", PrToFileName:="test.pdf"[/vba], but it corrupts the output file in half the versions of Acrobat. Some are patched, some are not. As for sendkeys, aside from being a not-so-good practice, the print prompt is modal so your sendkeys won't execute at the same time your app is up. To be honest, I don't thinkg there is a good way to do this. Even the add-in library doesn't expose options for it.

As a (bad) workaround:
Since it defaults to the workbook name you could print a copy of the workbook that has the name you want, but I'm not sure if the is the best plan or not. But here is the code to do so:

You have to set a reference to C:\Program Files\Adobe\Acrobat 8.0\PDFMaker\Office\AcrobatPDFMakerForOffice.tlb for this to work.

[vba]Option Explicit

Sub Test()
PrintToPDF "Foo"
End Sub
Sub PrintToPDF(defaultFileName As String)
Dim xlApp As excel.Application
Dim strTmpFl As String
Dim wbTmp As excel.Workbook
Dim lngAddIn As Long
Dim pmkr As AdobePDFMakerForOffice.PDFMaker
On Error GoTo Err_Hnd
strTmpFl = Environ$("Tmp")
If Right$(strTmpFl, 1) <> "\" Then
strTmpFl = strTmpFl & "\"
End If
strTmpFl = strTmpFl & defaultFileName & ".xls"
ThisWorkbook.SaveCopyAs strTmpFl
Set xlApp = New excel.Application
For lngAddIn = 1 To xlApp.COMAddIns.Count
If xlApp.COMAddIns(lngAddIn).Description = "Acrobat PDFMaker Office COM Addin" Then
Exit For
End If
Next
If lngAddIn > xlApp.COMAddIns.Count Then
Err.Raise vbObjectError, , "Cannot Find PDF Maker"
End If
Set pmkr = xlApp.COMAddIns(lngAddIn).Object
Set wbTmp = xlApp.Workbooks.Open(strTmpFl, False, True)
xlApp.Visible = True
wbTmp.Saved = True
pmkr.CreatePDF 0
Exit_Proc:
On Error Resume Next
xlApp.Visible = False
xlApp.DisplayAlerts = False
xlApp.Quit
Kill strTmpFl
Exit Sub
Err_Hnd:
MsgBox Err.Description, vbSystemModal, "Error: " & Err.Number
Resume Exit_Proc
End Sub[/vba]

[mdmackillop]

Does Adobe use an ini file to set the print paramaters? If so I posted here on manipulating that to set certain paramaters as required, which could include the output file name