I'm trying to write VBA code to automatically print a page in an excel
worksheet in PDF format and control it's name as well.

I'm hoping the answer lies in the Excel Print macro -

ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF on Ne09:"",,TRUE,,FALSE)"

I'm guessing that between one set of those commas is a place for a file name
etc.
Frankly I'd like to know what all the options are - what each space
designates.
Also, what would the syntax be for placing a string inside that?
Finally, I've notices that the "Ne09" designation for the printer changes
sometimes. How would I trap for that so that I could always be sure the
correct "Ne#" could be referenced. Thank you so much!

Excel 2007 record a ExecuteExcel4Macro, see the VBA help for Printout for the print options


You can use this function from Ole P Erlandsen

You use it in the macro like this

Sub Test()
Dim str As String
Dim strNetworkPrinter As String
str = Application.ActivePrinter

strNetworkPrinter = GetFullNetworkPrinterName("Adobe PDF")
If Len(strNetworkPrinter) 0 Then
Application.ActivePrinter = strNetworkPrinter
ActiveSheet.PrintOut
End If

Application.ActivePrinter = str
End Sub


Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String
Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long
strCurrentPrinterName = Application.ActivePrinter
i = 0
Do While i < 100
strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":"
On Error Resume Next ' try to change to the network printer
Application.ActivePrinter = strTempPrinterName
On Error GoTo 0
If Application.ActivePrinter = strTempPrinterName Then
GetFullNetworkPrinterName = strTempPrinterName
i = 100 ' makes the loop end
End If
i = i + 1
Loop
Application.ActivePrinter = strCurrentPrinterName ' change back to the original printer
End Function



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"TKoel" wrote in message ...
I'm trying to write VBA code to automatically print a page in an excel
worksheet in PDF format and control it's name as well.

I'm hoping the answer lies in the Excel Print macro -

ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF on Ne09:"",,TRUE,,FALSE)"

I'm guessing that between one set of those commas is a place for a file name
etc.
Frankly I'd like to know what all the options are - what each space
designates.
Also, what would the syntax be for placing a string inside that?
Finally, I've notices that the "Ne09" designation for the printer changes
sometimes. How would I trap for that so that I could always be sure the
correct "Ne#" could be referenced. Thank you so much!