Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print on PDF file
Hello
is there anyone who knows how to create a PDF file of the active worksheet through VBA code ? I tried many examples found on the net with no success... Cheers, A. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print on PDF file
On Mon, 27 Dec 2004 09:14:19 +0100, "Thanos"
wrote in microsoft.public.excel.programming: is there anyone who knows how to create a PDF file of the active worksheet through VBA code ? I tried many examples found on the net with no success... If you have full Acrobat (not just the Reader), you can use several methods: 1) Set the active printer to Distiller and simply use the method ..Printout - from memory, that will present a problem regarding the filename selection 2) Adobe Distiller provides its own print method: FileToPDF. If you don't have full Acrobat, you could use Ghostscript and RedMon. Then you have to manipulate the RedMon registry entry which holds the output filename. This will involve some nasty registry read/write actions and dealings with the obscure way Excel uses printer names and ports (different to any other MS Office application). I have implemented the latter here, where a macro prints a number of spreadsheets (10) rapidly to PDF files (saving paper). However, about once a week that process plays havoc with one particular PC's display: no refresh in any window -including TaskMgr-, completely nuts. Like you, I'm looking for a stable add-on which will provide the equivalent to Adobe's FileToPDF; the full Acrobat is just to expensive for that. The radical alternative, OpenOffice or Quattro, which both provide native PDF output, is unfortunately not practical for us. It might be for you, though. Let us know if you find anything. -- Michael Bednarek http://mbednarek.com/ "POST NO BILLS" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print on PDF file
Hello,
Try the method I use as under: 1. You have to first to some settings on the Adobe PDF printer. Open Adobe PDF's printer properites from printer and faxes. Clik on General Tab. There click on "Printing Preferences." Uncheck boxes 'View Adobe PDF Results' and 'Prompt for Adobe PDF Filename'. Click on OK. Click on 'Advanced' tab. There click on 'Printing Defaults'. Again uncheck above mentioned two boxes. 2. Find the full name of the Adobe PDF Printer as recognized by excel application. For this in excel open the file click on file menu- Print. Select Adobe PDF Printer and click on close. In a module, add following code to display the printer name: Sub showAdobePrinter() MsgBox Application.ActivePrinter End Sub. Note down the full printer name displayed. In my case it is 'Adobe PDF on Ne03' 3. Add following code to convert the sheet to PDF. Sub MakePDF() Dim lastPrinter As String, myPath As String Dim myFileName As String, Wb As Workbook myPath = "C:\Temp\" 'Only a temp. folder name (must exists) myFileName = "xyz" 'desired pdf filename WITHOUT extension myFullPath = myPath & myFileName & ".xls" Worksheets("Sheet1").Copy 'Select your sheetname Set Wb = ActiveWorkbook Wb.SaveAs myFullPath lastPrinter = Application.ActivePrinter Application.ActivePrinter = "Adobe PDF on Ne03:" Wb.PrintOut Wb.Close (False) Kill myFullPath Application.ActivePrinter = lastPrinter End Sub The xyz.pdf file gets saved in the default folder path used by Adobe PDF printer, defualt is My Documents. Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print on PDF file
Michael mentions Ghostscript.
I have had success with the free file downloads and instructions for use here at Dale Nurden's site. The use of Ghostscript is the key point with this method. http://www.rcis.co.za/dale/info/pdfguide.htm Saves first to single *.ps file which is then converted to *.pdf Returns one PDF file from multiple sheets. Gord Dibben Excel MVP On Mon, 27 Dec 2004 22:16:57 +1000, Michael Bednarek wrote: On Mon, 27 Dec 2004 09:14:19 +0100, "Thanos" wrote in microsoft.public.excel.programming: is there anyone who knows how to create a PDF file of the active worksheet through VBA code ? I tried many examples found on the net with no success... If you have full Acrobat (not just the Reader), you can use several methods: 1) Set the active printer to Distiller and simply use the method .Printout - from memory, that will present a problem regarding the filename selection 2) Adobe Distiller provides its own print method: FileToPDF. If you don't have full Acrobat, you could use Ghostscript and RedMon. Then you have to manipulate the RedMon registry entry which holds the output filename. This will involve some nasty registry read/write actions and dealings with the obscure way Excel uses printer names and ports (different to any other MS Office application). I have implemented the latter here, where a macro prints a number of spreadsheets (10) rapidly to PDF files (saving paper). However, about once a week that process plays havoc with one particular PC's display: no refresh in any window -including TaskMgr-, completely nuts. Like you, I'm looking for a stable add-on which will provide the equivalent to Adobe's FileToPDF; the full Acrobat is just to expensive for that. The radical alternative, OpenOffice or Quattro, which both provide native PDF output, is unfortunately not practical for us. It might be for you, though. Let us know if you find anything. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print on PDF file
On Mon, 27 Dec 2004 10:02:07 -0800, Gord Dibben <gorddibbATshawDOTca
wrote in microsoft.public.excel.programming: Michael mentions Ghostscript. I have had success with the free file downloads and instructions for use here at Dale Nurden's site. The use of Ghostscript is the key point with this method. http://www.rcis.co.za/dale/info/pdfguide.htm Saves first to single *.ps file which is then converted to *.pdf Returns one PDF file from multiple sheets. That still does not cover the OP's (and mine) request for a method which will work from VBA (without resorting to uglies like SendKeys). So far, the only methods I have found involve modifying the PDF-creating program's (Ghostscript, RedMod, PDFCreator) registry entries where they store the filename and path. Printing to a fixed filename/path and subsequent move/rename is probably also a possibility, but I haven't explored that yet. I suspect it might be problematic with multiple print jobs. I would appreciate any pointers to a COM add-in interface to produce PDF files. On Mon, 27 Dec 2004 22:16:57 +1000, Michael Bednarek wrote: On Mon, 27 Dec 2004 09:14:19 +0100, "Thanos" wrote in microsoft.public.excel.programming: is there anyone who knows how to create a PDF file of the active worksheet through VBA code ? I tried many examples found on the net with no success... If you have full Acrobat (not just the Reader), you can use several methods: 1) Set the active printer to Distiller and simply use the method .Printout - from memory, that will present a problem regarding the filename selection 2) Adobe Distiller provides its own print method: FileToPDF. If you don't have full Acrobat, you could use Ghostscript and RedMon. Then you have to manipulate the RedMon registry entry which holds the output filename. This will involve some nasty registry read/write actions and dealings with the obscure way Excel uses printer names and ports (different to any other MS Office application). I have implemented the latter here, where a macro prints a number of spreadsheets (10) rapidly to PDF files (saving paper). However, about once a week that process plays havoc with one particular PC's display: no refresh in any window -including TaskMgr-, completely nuts. Like you, I'm looking for a stable add-on which will provide the equivalent to Adobe's FileToPDF; the full Acrobat is just to expensive for that. The radical alternative, OpenOffice or Quattro, which both provide native PDF output, is unfortunately not practical for us. It might be for you, though. Let us know if you find anything. -- Michael Bednarek http://mbednarek.com/ "POST NO BILLS" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
my excel is converting all my print jobs to print to file why? | Excel Discussion (Misc queries) | |||
How to format the extension less file to print with Dos's Print Command | Excel Discussion (Misc queries) | |||
Can view file in print preview, but not print layout.... | Excel Discussion (Misc queries) | |||
print and print preview not active in file drop down | Excel Discussion (Misc queries) | |||
How do I print from a file created from sending a print to a file | Excel Discussion (Misc queries) |