PDF button
 

hi all, i'd like to create a button on my worksheet that will create a pdf
file of the current worksheet when pressed, can this be done?

Hi Kwanjangnim

If you cannot currently create a pdf file manually, you need to go to this
site and set everything up.
http://www.rcis.co.za/dale/info/pdfguide.htm

Then, attach this macro to a button on one of your toolbars

Sub PrintActiveSheetAsPDF()
'Set a reference to Acrobat Distiller
Dim PSFileName As String, PDFFileName As String
Dim myPDF As PdfDistiller, x As String, y As String
Dim wb As Workbook, iprinter As String, ws As Worksheet
iprinter = Application.ActivePrinter

'Amend as appropriate
Application.ActivePrinter = "Adobe PDF on Ne02:"

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

'Where to save the pdf file
If wb.Path = "" Then
x = CreateObject("WScript.Shell").SpecialFolders("Desk top") & "\"
y = wb.Name
Else
x = wb.Path & "\"
y = Left(wb.Name, Len(wb.Name) - 4)
End If
PSFileName = x & y & ".ps"
PDFFileName = x & y & ".pdf"

'Print the file
ws.PrintOut , prtofilename:=PSFileName
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

'Clean up
On Error Resume Next
Kill Left(PSFileName, Len(PSFileName) - 2) & "log"
Kill PSFileName
Application.ActivePrinter = iprinter

'Amend as appropriate to open the file
Shell "C:\Program Files\Adobe\Acrobat 7.0\Reader\AcroRd32.exe " +
PDFFileName, 1
End Sub
--


XL2003
Regards

William



"Kwanjangnim" wrote in message
...
hi all, i'd like to create a button on my worksheet that will create a pdf
file of the current worksheet when pressed, can this be done?

i already have the postscript drivers installed as this was done when i
installed acrobat, this came with the pdf toolbar defaults, what i wanted to
do was have a custume made button that i could attach a macro to that would
print the active worksheet out as a pdf file, i tried the macro you wrote but
this didn't work, since i don't know how to write macros personally i also
tried the record function but this also failed. the other thing to note is
that i have a mac computer so this macro needs to be cross platform, thanks
for your help so far

"William" wrote:

Hi Kwanjangnim

If you cannot currently create a pdf file manually, you need to go to this
site and set everything up.
http://www.rcis.co.za/dale/info/pdfguide.htm

Then, attach this macro to a button on one of your toolbars

Sub PrintActiveSheetAsPDF()
'Set a reference to Acrobat Distiller
Dim PSFileName As String, PDFFileName As String
Dim myPDF As PdfDistiller, x As String, y As String
Dim wb As Workbook, iprinter As String, ws As Worksheet
iprinter = Application.ActivePrinter

'Amend as appropriate
Application.ActivePrinter = "Adobe PDF on Ne02:"

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

'Where to save the pdf file
If wb.Path = "" Then
x = CreateObject("WScript.Shell").SpecialFolders("Desk top") & "\"
y = wb.Name
Else
x = wb.Path & "\"
y = Left(wb.Name, Len(wb.Name) - 4)
End If
PSFileName = x & y & ".ps"
PDFFileName = x & y & ".pdf"

'Print the file
ws.PrintOut , prtofilename:=PSFileName
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

'Clean up
On Error Resume Next
Kill Left(PSFileName, Len(PSFileName) - 2) & "log"
Kill PSFileName
Application.ActivePrinter = iprinter

'Amend as appropriate to open the file
Shell "C:\Program Files\Adobe\Acrobat 7.0\Reader\AcroRd32.exe " +
PDFFileName, 1
End Sub
--


XL2003
Regards

William



"Kwanjangnim" wrote in message
...
hi all, i'd like to create a button on my worksheet that will create a pdf
file of the current worksheet when pressed, can this be done?