Print pdf to sheet name in excel macro

[netpilot]

First post.
Can anybody please explain how to print to pdf from within an Excel macro, using the worksheet name and original file path as output.
Thanks for any suggestions.

[anglerdk]

the eassiest way is to use Excel to put it in the footer. in page settings..

you can make Excel write footer and header with sheet name and path..

alternative - you have to code to the bullzip printer a watermark in the buttom of the page.

see syntax here: http://www.biopdf.com/guide/settings.php

sorry I'm sitting in the train on my way home - and its 2 min for my stop...... (therefore there is a lot of danish in the comments)

[code]

Sub PrintSheetAsPDFwithBullZip()
'must add a reference to BullZip
Dim myobject As New Bullzip.PDFPrinterSettings
Dim SavePath As String, FileName As String
SavePath = "c:\temp\" ' Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\Desktop\"
FileName = "Testfil" ' InputBox("Save PDF to desktop as:", "Sheet '" & ActiveSheet.Name & "' to PDF...", ActiveSheet.Name)
If LCase(Right(FileName, 4)) <> ".pdf" Then FileName = FileName & ".pdf"

'see default settings in
'Environ ("HOMEDRIVE") & Environ("APPDATA") & "\Bullzip\PDF Printer\settings.ini"

' ############## Den gemte fil inkl vandmærke og synsfil - the file to write to - your output
myobject.SetValue "output", SavePath & FileName

watermarkTXT = ActiveSheet.Name & " - " & ThisWorkbook.FullName 'this is here you write what you want to write on the page..

'######## Laver vandmærke ############### - se http://www.biopdf.com/guide/settings.php
myobject.SetValue "watermarktext", watermarkTXT




myobject.SetValue "WatermarkVerticalPosition", "top" 'vertical placement
myobject.SetValue "WatermarkHorizontalPosition", "right" 'h placering
myobject.SetValue "WatermarkVerticalAdjustment", "1" 'sætter den 1% længere ned på siden - 1% lower, so you have a margen
myobject.SetValue "WatermarkHorizontalAdjustment", "10"
myobject.SetValue "WatermarkColor", "#000000" 'sort farve tekst - black
myobject.SetValue "WatermarkSize", "3"
myobject.SetValue "WatermarkRotation", "0"
myobject.SetValue "WatermarkOutlineWidth", "0"


' ########## Filen der skrives i "###################### - the file to write in..
' myobject.SetValue "Superimpose", "c:\temp\syn.pdf" ' the background file.. you doesnt need it anyway - I used it to write a watermark in a file that allready existed...



myobject.SetValue "SuperimposeLayer", "top" 'viser bare hvor den lave laget - sikkert ikke nødvendigt
myobject.SetValue "ShowPDF", "no" 'viser ikke PDF bagefter - doesnt show pdf afterwards
myobject.SetValue "ShowSaveAS", "never" ' viser ikke save as
myobject.SetValue "ShowProgressFinished", "no" 'viser ikke ballontip
myobject.SetValue "showsettings", "never"
myobject.WriteSettings (True) 'writes the settings in a runonce.ini that it immediately deleted after being used.

'change to bullzip printer...
If InStr(ActivePrinter, "Bullzip") = 0 Then
Dim storeprinter$, PrinterChanged As Boolean
PrinterChanged = True
storeprinter = ActivePrinter
ActivePrinter = GetFullNetworkPrinterName("Bullzip") 'This is the name of my bullzip printer..
End If

ActiveSheet.PrintOut

If PrinterChanged Then ActivePrinter = storeprinter
End Sub

'the following code is from http://www.erlandsendata.no/english/ind ... ngeprinter
Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String
' returns the full network printer name
' returns an empty string if the printer is not found
' e.g. GetFullNetworkPrinterName("HP LaserJet 8100 Series PCL")
' might return "HP LaserJet 8100 Series PCL on Ne04:"
Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long
strCurrentPrinterName = Application.ActivePrinter
i = 0
Do While i < 100

' Sprog bug - da dansk hedder det ikke on , men på
If Application.LanguageSettings.LanguageID(msoLanguageIDUI) = 1030 Then
strTempPrinterName = strNetworkPrinterName & " på Ne" & Format(i, "00") & ":"
Else

strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":"

End If

On Error Resume Next ' try to change to the network printer
Application.ActivePrinter = strTempPrinterName
On Error GoTo 0
If Application.ActivePrinter = strTempPrinterName Then
' the network printer was found
GetFullNetworkPrinterName = strTempPrinterName
i = 100 ' makes the loop end
End If
i = i + 1
Loop
' remove the line below if you want the function to change the active printer
Application.ActivePrinter = strCurrentPrinterName ' change back to the original printer
End Function
[/code]