Hi, i have written a macro to print seperate excel worksheets into seperate pdf files with the names defined wtihin the macro so it runs from start to finish without interruption.
I think i'm nearly there with it but I can't seem to get it to work. If you could take a look and let me know where i'm going wrong here I would be very grateful.
Kind Regards
Why use Sendkeys?
Which PDF program are you using?
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
i'm using acrobat distiller 5.0
I thought I would have to use sendkeys for this.... is there another way?
You have a full verion of Acrobat?
If so search the Forum, there's code already published here. It's best to avoid sendkeys.
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
I do have this code also, is this what you are referring to? I was unable to get this to work also - it is a little advanded for me... if you could give me some feedback here. Appreciate your help!
Sub Create_PDF()
'Dom Hill, June 2008
'This code has only been tested with Adobe Acrobat 6.0 Professional
'For this code to work the Adobe Distiller VBA library must be enabled
'Select Tools...References in the VBA editor and check it from the list
Dim tempPDFFileName As String
Dim tempPSFileName As String
Dim tempPDFRawFileName As String
Dim tempLogFileName As String
'define file name and path in subdirectory of file where code workbook
'is stored called 'Print Jobs' and picks file name up from a cell
'named as a named range called 'File_Name'
tempPDFRawFileName = ThisWorkbook.Path & "\Print Jobs\" & Range("File_Name")
tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"
tempLogFileName = tempPDFRawFileName & ".log"
'Prints worksheet 'Output' as a pdf, an array of sheets can be printed if required
Sheets("Output").PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
Dim mypdfDist As New PdfDistiller
mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
Kill tempPSFileName
Kill tempLogFileName
End Sub
Why does it not work?
What error messages are generated?
Have you set the references in the VB Editor?
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
That'll be mine...
I've adapted it to your example with some code to loop through the worksheets and print each one. Just run the PDF_Sheets macro.
I had to add the Adobe Distiller library to VBA, mine is for Acrobat 6.0 so you might need to check that it's the same for your version of Adobe (Tool...References in the VBA editor).
Let us know how you get on with it.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Thanks,
I ran the macro and it printed the first sheet via the printer, it goes on:
I'm geting a "Run-time error '429"
ActiveX component can't create object.
When I go to visual basic it references:
mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
Did you check the references that the Adobe Distiller library is available? Do you have a printer called Adobe PDF installed?
I'm afraid I've no idea what the differences are between Acrobat 5.0 and 6.0 and whether this code works on versions prior to 6.0.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Did you check the references that the Adobe Distiller library is available? - Yes
Do you have a printer called Adobe PDF installed? - No
I assume you have an Adobe printer installed as part of Acrobat? I gather from looking here that the name was changed for 6.0.
If so you will need to change this line in the code where it specifies the ActivePrinter to reflect the name of your printer driver:
wsPrint_Sheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _ printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
Dom
Last edited by Domski; 09-27-2010 at 09:32 AM.
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
What's the list of available printers?
I would actually install a frre pdf printer such as PDFCreator & then use Ken Puls' code
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
I did that but i'm still showing up errors:
Sub Create_PDF(wsPrint_Sheet As Worksheet) 'Dom Hill, June 2008 'This code has only been tested with Adobe Acrobat 6.0 Professional 'For this code to work the Adobe Distiller VBA library must be enabled 'Select Tools...References in the VBA editor and check it from the list Dim tempPDFFileName As String Dim tempPSFileName As String Dim tempPDFRawFileName As String Dim tempLogFileName As String tempPDFRawFileName = "C:\" & wsPrint_Sheet.Name tempPSFileName = tempPDFRawFileName & ".ps" tempPDFFileName = tempPDFRawFileName & ".pdf" tempLogFileName = tempPDFRawFileName & ".log" 'Prints worksheet as a pdf wsPrint_Sheet.PrintOut Copies:=1, preview:=False, Application.ActivePrinter = "Acrobat Distiller on Ne03:", _ printtofile:=True, Collate:=True, prtofilename:=tempPSFileName Dim mypdfDist As New PdfDistiller mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, "" Kill tempPSFileName Kill tempLogFileName End Sub
You might want to try the code in a new workbook other than the example one I gave and add your own references in. I know that they are often not backwards compatible so this could be causing you problems. Other than that I'm not sure I can suggest much else without seeing what is going on.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
ok i will try that. i'm not sure what you mean here...can you just confirm what references I might/will need to add...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks