Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print to PDF with Acrobat 7.0
I have Adobe Acrobat 7.0 which is set as my default printer. I need to know
how to change the name of the print out after a macro is run changing the data. What I have so far: Application.DisplayAlerts = False Application.Run "PERSONAL.xlsb!QuoteAge24" Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")).Select Sheets("1").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Adobe PDF on Ne04:", Collate:=True Sheets("Pivot").Select I have tried several ways of adding the file name/location for the output with no success. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print to PDF with Acrobat 7.0
It would be very nice if we could program directly like this; however, with
venturing into the adobe api programming can become more difficult. Here is possibly a better way. Acrobat comes with Distiller. Distiller has the ability to "watch" a folder and when it finds a postscript file (*.ps) in the watch file "In" folder, it will "automatically" convert it to a PDF and move the results to the watch file "Out" folder. To utilitze this functionality all you need do is print your Excel files to a PostScript file in the distiller watch file "In" folder, and wait until it is in the "Out" folder, then do with it what you like. Do this to define a PostScrip Printer: Define a new local printer named "PScript". Use Port "File: (Print to File)". Find a PostScript printer to use as the Printer Model Number, any PostScript Printe will do. I use "HP Deskjet 1200C/PS". Do this to set up Distiller Watch files: Start up Acrobat Distiller and define a "watch folder". Let's say you name it "C:\ReportFiles". When you define it as a "watch folder", distiller will add 2 subfolders named "In", and "Out". Also in Distiller you have the option of "Deleting the postscript file", or "Moving it to the Out folder". Make you choice. Now you have a Windows printer that you can control through VBA code. Here is basic VBA print to file code: Application.ActivePrinter = "PScript on FILE:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PScript on FILE:", PrintToFile:=True, Collate:=True, PrToFileName:="C:\ReportFiles\In\PrintFile.ps" To open the PDF file use this code: Application.FollowHyperlink C:\ReportFiles\In\PrintFile.ps To launch Acrobat Distiller programmatically use this code: shell C:\Program files/Adobe/Acrobat 7.0\Distillr\acrodist.exe" Let me know how it works!! "Richard Ward" wrote: I have Adobe Acrobat 7.0 which is set as my default printer. I need to know how to change the name of the print out after a macro is run changing the data. What I have so far: Application.DisplayAlerts = False Application.Run "PERSONAL.xlsb!QuoteAge24" Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")).Select Sheets("1").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Adobe PDF on Ne04:", Collate:=True Sheets("Pivot").Select I have tried several ways of adding the file name/location for the output with no success. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print to PDF with Acrobat 7.0
I followed the intructions provided, the macro runs, but there is no output
file in the in, or out folder. Here is what I have: Application.DisplayAlerts = False Application.Run "PERSONAL.xlsb!QuoteAge24" Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PScript", PrintToFile:=True, Collate:=True, PrToFileName:="C:\ReportFiles\In\TestPrintFile.ps" Sheets("Pivot").Select "Richard Ward" wrote: I have Adobe Acrobat 7.0 which is set as my default printer. I need to know how to change the name of the print out after a macro is run changing the data. What I have so far: Application.DisplayAlerts = False Application.Run "PERSONAL.xlsb!QuoteAge24" Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")).Select Sheets("1").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Adobe PDF on Ne04:", Collate:=True Sheets("Pivot").Select I have tried several ways of adding the file name/location for the output with no success. Any help would be appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print to PDF with Acrobat 7.0
Your code looks good. I copied it and tested it. It worked fine. These are
the few changes I made: ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PScript", PrintToFile:=True, Collate:=True, PrToFileName:="C:\ReportFiles\in\TestPrintFile.ps" Are you sure your code is getting to this statement? You might try stepping through the code to verify. "Richard Ward" wrote: I followed the intructions provided, the macro runs, but there is no output file in the in, or out folder. Here is what I have: Application.DisplayAlerts = False Application.Run "PERSONAL.xlsb!QuoteAge24" Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PScript", PrintToFile:=True, Collate:=True, PrToFileName:="C:\ReportFiles\In\TestPrintFile.ps" Sheets("Pivot").Select "Richard Ward" wrote: I have Adobe Acrobat 7.0 which is set as my default printer. I need to know how to change the name of the print out after a macro is run changing the data. What I have so far: Application.DisplayAlerts = False Application.Run "PERSONAL.xlsb!QuoteAge24" Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")).Select Sheets("1").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Adobe PDF on Ne04:", Collate:=True Sheets("Pivot").Select I have tried several ways of adding the file name/location for the output with no success. Any help would be appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print to PDF with Acrobat 7.0
I got all the way through the code, I even got prompted for a file name (I
didn't expect this) and there was nothing in the "in" or "out" folders. "Al" wrote: Your code looks good. I copied it and tested it. It worked fine. These are the few changes I made: ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PScript", PrintToFile:=True, Collate:=True, PrToFileName:="C:\ReportFiles\in\TestPrintFile.ps" Are you sure your code is getting to this statement? You might try stepping through the code to verify. "Richard Ward" wrote: I followed the intructions provided, the macro runs, but there is no output file in the in, or out folder. Here is what I have: Application.DisplayAlerts = False Application.Run "PERSONAL.xlsb!QuoteAge24" Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PScript", PrintToFile:=True, Collate:=True, PrToFileName:="C:\ReportFiles\In\TestPrintFile.ps" Sheets("Pivot").Select "Richard Ward" wrote: I have Adobe Acrobat 7.0 which is set as my default printer. I need to know how to change the name of the print out after a macro is run changing the data. What I have so far: Application.DisplayAlerts = False Application.Run "PERSONAL.xlsb!QuoteAge24" Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")).Select Sheets("1").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Adobe PDF on Ne04:", Collate:=True Sheets("Pivot").Select I have tried several ways of adding the file name/location for the output with no success. Any help would be appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print to PDF with Acrobat 7.0
You should not be prompted for a file. They command supplies the file name.
You have something wrong in the syntax. What I found was that this entire command could not be continued to multiple lines and work properly. My guess is that if your command is on multiple lines and your last line is the file name it's not reading that line as part of the command. Try bunching it together on 1 line. Did you compile the code (Debug, compile)? Was compile successful? "Richard Ward" wrote: I got all the way through the code, I even got prompted for a file name (I didn't expect this) and there was nothing in the "in" or "out" folders. "Al" wrote: Your code looks good. I copied it and tested it. It worked fine. These are the few changes I made: ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PScript", PrintToFile:=True, Collate:=True, PrToFileName:="C:\ReportFiles\in\TestPrintFile.ps" Are you sure your code is getting to this statement? You might try stepping through the code to verify. "Richard Ward" wrote: I followed the intructions provided, the macro runs, but there is no output file in the in, or out folder. Here is what I have: Application.DisplayAlerts = False Application.Run "PERSONAL.xlsb!QuoteAge24" Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="PScript", PrintToFile:=True, Collate:=True, PrToFileName:="C:\ReportFiles\In\TestPrintFile.ps" Sheets("Pivot").Select "Richard Ward" wrote: I have Adobe Acrobat 7.0 which is set as my default printer. I need to know how to change the name of the print out after a macro is run changing the data. What I have so far: Application.DisplayAlerts = False Application.Run "PERSONAL.xlsb!QuoteAge24" Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20")).Select Sheets("1").Activate ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:="Adobe PDF on Ne04:", Collate:=True Sheets("Pivot").Select I have tried several ways of adding the file name/location for the output with no success. Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can't print to adobe acrobat | Charts and Charting in Excel | |||
Print to PDF through Acrobat 6.0 | Excel Programming | |||
excel/vba & acrobat 7.0 pro - Print multiple sheets to 1 PDF | Excel Programming | |||
Print Acrobat Reader PDF file from VBA | Excel Programming | |||
Print to Adobe Acrobat (2 ws to one file) | Excel Programming |