Print pdf to sheet name in excel macro

About the programming interface for the virtual PDF printer driver.

Moderator: jr

Post Reply
netpilot
Posts: 2
Joined: Tue Jun 23, 2009 8:31 am

Print pdf to sheet name in excel macro

Post by 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
Posts: 6
Joined: Tue Sep 15, 2009 6:22 pm

Re: Print pdf to sheet name in excel macro

Post by 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]
Post Reply