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
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
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.
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?
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
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:
Please Login or Register to view this content.
Dom
Last edited by Domski; 09-27-2010 at 09:32 AM.
What's the list of available printers?
I would actually install a frre pdf printer such as PDFCreator & then use Ken Puls' code
I did that but i'm still showing up errors:
Please Login or Register to view this content.
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
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...
The only one I need with Acrobat 6.0 is the Acrobat Distiller reference.
Dom
The problem I think is with this piece of code:
Also - it is still printing on my default printer and not the pdf..... any ideas?Please Login or Register to view this content.
Any ides on the code:
I think I might be getting something mixed up here:Please Login or Register to view this content.
Please Login or Register to view this content.
Looks fine but I've still no idea if it will work in Adobe 5.0. I'm guessing not from the problems you're having.
Dom
OK - how about PDF Creator. Do you know if this would work for that? what changes would I need here... thanks
Never had cause to use it myself but all the info is there on the link that Roy posted.
Dom
I have just run it again and this time i get the following error:
Run-time error 429
ActiveX component can’t create object
any ideas?
How is that different from the above error you were getting the other day?Originally Posted by omarq
Dom
ahhh -sorry. i've been looking at this for so long i'm getting fish for brains. apologies
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks