Link to home
Start Free TrialLog in
Avatar of pradeepvarma
pradeepvarma

asked on

Saving an Access report to a PDF (Acrobat 6.0) document with a custom file name

Hi,

 I am using Acrobat 6.0. Initially I have tried to save an Access report to a PDF file using the Acrobat Distiller but came across an error message “ActiveX component cannot create object –429”. I have tried all sorts of things based on information that I got but not able to get rid of the same.

Alternatively, I have turned the PromptForFileName off in the printer preferences. I have created a value name in which I am able to store the path and file name in the registry and also a value by name persistent that ensures that the contents are not wiped out. I have used the following code.

Application.Printer = Application.Printers("Adobe PDF")

SaveSetting "XYZ", "XYZ", "PDFFileName", pdfPath & filename & ".pdf"

DoCmd.OpenReport ReportName, acNormal

Set Application.Printer = Nothing

The value name PDFFileName is getting the value passed by me (path and the file name) and the PDF file is getting generated. But the file name is the default name of the report and not the value that I am passing to PDFFileName. I have the following questions in this regard:

1.      Can I make the file name to be the one that I am passing to a registry value? If yes, then how?

2.      Can I pass the location where the file is to be saved in the above way without changing the port for the Adobe PDF printer? If yes, then how?

I would appreciate if someone can throw light on this method of saving an access report to a PDF format.

Thanks :-),

Pradeep Varma
ASKER CERTIFIED SOLUTION
Avatar of puppydogbuddy
puppydogbuddy

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of puppydogbuddy
puppydogbuddy

Avatar of pradeepvarma

ASKER

Hi puppydogbuddy,

Thanks for the quick replies.

Will check if it works and then get back to you.

Thanks,

Pradeep Varma
Hi puppydogbuddy,

I was trying to implement the code but do not have a printer called PDF995. I am using Adobe Acrobat Elements6.

I am using the printer "ADOBE PDF" and did not find locate the PDF995.ini and the pdfsync files.

I have taken the final code from the below link:

https://www.experts-exchange.com/questions/21802440/Output-to-PDF-using-multiple-reports.html#16385849

Am I doing something wrong and do I need to install something additional to what I have (Adobe Acrobat Elements6).

Please advise.

Thanks and Regards,

Pradeep Varma
Hello puppydogbuddy,

I was also researching the probable reasons for the ActiveX error when I am trying to instantiate the PDFDistiller bject reference. I checked the regisrty and found that I do not have permissions on the Acrodist (HKEY_CLASSES_ROOT\Acrodist). I requested for the permissions. Do you have any idea of any other registry keys that I might need permission on to automate the Acrobat Distiller (Acrodist.exe).

If this problem is resolved, the steps to generate the Access report in Acrobat 6.0 becomes easy.

I am sorry in case I am changing the directions. I went through the adobe site and they have mentioned that with Acrobat6.0 it is much simplified (generating a PDF file from a report) and with 7.0 it gets done by adding a registry key by name PrinterJobControl.

Thats what has prompted me to probe the reasons for the failure of attempting to create references to the  distiller.

Please advise.

Thanks,

Pradeep Varma
Pradeep,
I am not certain, but I think there is also a registry key for the distiller in the drivers section of the registry. To help you out, I found the following link where you can download the registry settings to any folder on your computer.  Just click the windows icon.

http://www.ghentpdfworkgroup.org/en/specifications_2004_compliant_applications.php#Adobe_Acrobat_Distiller


The first solution I orginally referred you to in this post is strictly for the pdf995 driver.  Should you wish to pursue this solution, I believe I can get you a link to a free pdf995 driver.
Hello  puppydogbuddy,

Thanks for your suggestion but i cannot install any custom driver to make this work. We have Acrobat 6.0 installed all over and I need to make the same working rather than installing another driver.

I shall go through the link give by know and update you with my results.

Thanks,

Pradeep Varma
Hello puppydogbuddy,

I did go through the link and clicked on the windows Icon that is there beside the Acrobat Distiller 6 settings. It gave me a zip file Distiller Settings 1v2.zip which when extracted gave me some JOBOPTION files. I found that there are some JOBOPTION files and when I went into Edit preferences of Acrobat, I could find that that JOBOPTION for Convert To PDF--> Microsoft Office is Standard. On the whole, I could not really understand as to what is to be done with the zip file that I got from the above link.

Please advise.

Regards,

Pradeep Varma
The zip file gives you a list of the registry settings you need for distiller. Now you have to enter those settings into the registry one by one.
Hello puppydogbuddy,

All the zip file gave is a list of JOBOPTION files. I dont see anything related to registry. The filenames are as follows:

MagazineAds_1v2.joboptions
NewspaperAds_1v2.joboptions
SheetCmyk_1v2.joboptions
SheetSpotHiRes_1v2.joboptions
SheetSpotLoRes_1v2.joboptions
WebCmykHiRes_1v2.joboptions
WebCmykNews_1v2.joboptions
WebSpotHiRes_1v2.joboptions
WebSpotNews_1v2.joboptions

I opened one of them in a word editor to see if I can find some registry related information but cannot see one.

All they contain is settings for the Acrobat Editor.

Please let me know if i downloaded a wrong file.

Thanks,

Pradeep Varma
If you downloaded the Acrobat 6 Distiller Settings  and the screengrabs, it should have been the correct ones.  I guess they have the settings encrypted. I will get back to you with another link.
Hi puppydogbuddy,

I did not download the screengrabs. Probably I was missing that. I am doing that now. However the link you gave me tells me how to change the file path. Thats not an issue I can always make the printer port set to a particular path because the reports are any how saved into one fixed folder. The  real challenge that is troubling me is giving the file a custom name.

I appreciate your patience in this regard.

I will get back to you with my findings.

Thanks,

Pradeep Varma
Hello puppydogbuddy,

I went through the screengrabs as well. As i told you earlier if we copy these settings (JOBOPTION files) into the settings folder of Acrobat, you see the settings that apply to Acrobat by going into Edit--> Preferences. Probably by setting these options, they internally set some registry settings. But I have a full version of Acrobat Elements 6. When that is the case I should be able to use the distiller and automate the process which works to only 4 lines. I am sure I am missing some setting which is causing the error "ActiveX Component Can't Create Object - 429".

Just now I found another DLL(AdobePDFMakerX.dll) with a type library. When I registered this dll and selected it in the references, I found that it exposes a method called "DistillFile" which again takes 4 params which ultimately generate the file in PDF format. Even that is giving me the same error.

You have been very helpful so far and was very quick in reponding to my queries. Please let me know in case you are running out of ideas. If thats the case I will close the question.

Thanks and Have a great time,

Pradeep Varma
Pradeep,

go to the Adobe link I gave you this AM and type in    object 429      in the search field at the top right.  I think it relates to XML.  Review the material provided. I don't think XML is required, so maybe you ready to try it out.
Pradeep,
I went back to the download link and there are 4 files that you should have downloaded, 2 for Distiller6 and 2 for screen grabs.  That may be why you have obj 429 problems.
Hi  puppydogbuddy,

Do you mean to say that those files are essential for automation of the distiller. I am sure they should be included in Acrobat Elements6 if it was. Anyhow I will go back and check it.

Thanks,

Pradeep Varma
Hello PuppyDogBuddy,

Nothing seems to work really. I still come across the same error "ActiveX Component Can't Create Object - 429".

The question has been open for quite sometime. You have been very helpful so far.

Thanks a lot for the same.

Cheers,

Pradeep Varma
Pradeep,
see this link:  http://www.adobe.com/cfusion/search/index.cfm?KMC=0&STARTNUM=1&TERM=object%20error%20429&LOC=en%5Fus

It references 30 posts by Adobe in response to object 429 error.  You should review one by one to see if any apply to you.  Recommendatons from Adobe for fixing the error include items such as your regional time settings are incorrect.....change to Pacific....time.
Pradeep,
this should resolve your problem with error 429....let me know what haappened.

 http://www.tek-tips.com/faqs.cfm?fid=2882

                      PDB
Hello puppydogbuddy,

I really appreciate your patience in this regard.

I did go to the above link and tried the steps to see if that resolves the issue. But it did not. However while searching for more information on the same i came across the following link:
 
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b828550

As mentioned in it, i have booted without the services (msconfig.exe) and then rebooted the computer with all the services loaded. Suprisingly that solved my problem of the ActiveX Object error.

Dim ObjPDF as PdfDistiller

Set ObjPDF = New PdfDistiller

DoCmd.OpenReport "Report Name", acViewPreview

intTest = ObjPDF.FileToPDF("Report Name", "Custom Report Name with Path","")

However intTest returns a 0 and the file is not created. I checked the AcrobatDistiller API Reference and it says that 0 means that the parameters are invalid.

Am I doing something wrong here or is it a must that it should only be a postscript file. If thats the case, I am able to get an access report in PDF format with the following lines of code.

Application.Printer=Application.Printers("ADOBE PDF")
 
DoCmd.OpenReport "Report Name", acViewNormal

Set Application.printer =Nothing

You have been very helpful and I really appreciate your patience. Please advise if I am missing something.

FYI --> I am able to create an instance with early binding and late binding methods without any problem now.

Thanks,

Pradeep Varma



Hi,

Just wanted to add that by using the below code the report is generated in PDF format but it is generated with the default report name.  I want to give the report a custom name as you are already aware of.

Application.Printer=Application.Printers("ADOBE PDF")
 
DoCmd.OpenReport "Report Name", acViewNormal

Set Application.printer =Nothing

Thanks,

Pradeep Varma
Pradeep,
Report Name and path should be variables passed from report open event or other code.

Dim ReportName As String
Dim strPath as String
_____________________________________________________

DoCmd.OpenReport ReportName, acViewNormal
ObjPDF.FileToPDF(ReportName, strPath & ReportName & ".pdf","")

Let me know how you made out.
Hi puppydogbuddy,

can't I have it in the following way:

Dim Report as String, strPath as String, strFileName as string

Report="X"

strPath="C:\Reports\"

strFileName="Y.pdf"

ObjPDF.FileToPDF(Report,strPath & strFileName,"")

Thanks for the help

Pradeep Varma
I am passing it as variables to the FileToPDF method,even then it returns a zero.

Thanks ,

Pradeep Varma
Yes, that should work also....but it looks you will be entering all the info at runtime instead earlier binding.
you probably are updating a pdf infile and not a pdf outfile......somewhere in the distiller documentation, it should have info about a pdf outfile (this is different then the Access ouput file)
Pradeep,
Here is the part of the pdf995 printer ini file that you need to find the equivalent to in the distiller.  You have some of it, but not all of it.  Let me know how its going.

 save current settings from the PDF995.ini file
tmpoutputfile = ReadINIfile("PARAMETERS", "Output File", iniFileName)
tmpAutoLaunch = ReadINIfile("PARAMETERS", "Autolaunch", iniFileName)

' remove previous pdf if it exists
On Error Resume Next
Kill outputfile
On Error GoTo Cleanup

' setup new values in PDF995.ini
x = WritePrivateProfileString("PARAMETERS", "Output File", outputfile, iniFileName)
x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", "0", iniFileName)

' change the default printer to PDF995
' if running on Access 2000 or earlier, comment out the next two lines
Set tmpPrinter = Application.Printer
Application.Printer = Application.Printers("PDF995")
PS: forgot to tell you that in looking at Parameters, you should be passing only 2 to the output file, and you are passing 3 with your code. and I also passed 3 with my adaption of your code, which I've corrected below:


                         Your code
<<<Dim Report as String, strPath as String, strFileName as string>>> has 3 parameters

__________________________________________________________________________
                          My code
<<<ObjPDF.FileToPDF(ReportName, strPath & ReportName & ".pdf","")<<< 3 parameters
                          should have been
<<<<ObjPDF.FileToPDF(ReportName, strPath & ReportName & ".pdf")  <<< 2 parameters
puppydogbuddy,

It does not accept two parameters and gives a compile error which says "Aurgument Not Optional".

In the meanwhile I have found another way with which it is creating a PDF file.

Dim strReport as String,strPath as String, strFileName as String

strPath="C:\Reports\"

strFileName="Y.pdf"

strReport="X"

Application.Printer=Application.Printers("ADOBE PDF")

DoCmd.OpenReport strReport,acViewPreview

Reports(strReport).Capition=strPath & strFileName

DoCmd.PrintOut

Set Application.Printer = Nothing

This code is creating the PDF file in the path specified but the only problem is that the created file pops up and I do not want that to happen. The convenience with FileToPDF is that the file gets created silently behind the scenes.

I am looking at acheiving the task using FileToPDF and working in that regard.

If you have any ideas, please let me know.

Thanks and Regards,

Pradeep Varma
puppydogbuddy,

Another Interesting result:

I have created a key by name application under the following path in the registry as explained in the Acrobat Distiller API. I stored the path in it C:\TOPDF\

HKEY_CURRENT_USER\Software\Adobe\Acrobat Distiller\PrinterJobControl

I went to the printing preferences and turned off the option View Adobe PDF Results.

Then this code silently creates an access report in PDF format in the required path.

Application.Printer=Application.Printers("ADOBE PDF")
DoCmd.PrintReport ReportName, acviewNormal

I have acheived the required task and really appreciate your help. In case you have any ideas which might make FileToPDF work, It would be great.

Thanks,

Pradeep Varma
Pradeep,
Try both of the two syntaxes below and let me know.

DoCmd.OutputTo acOutputReport, ReportName,, strPath & ReportName & ".pdf", False
                                              Or
        FileToPDF(ReportName, strPath & ReportName & ".pdf","")
Hello  puppydogbuddy,

I tested your suggestions and the following are the results:

DoCmd.OutputTo acOutputReport, ReportName,, strPath & ReportName & ".pdf", False --> with the format argument left unfilled, the format dialog box comes up. If we do not select any format and click on cancel, it comes up with an error "The OutputTo action was cancelled". If select the RTF format, when we try opening the .pdf file, it gives an error "Acrobat could not open the "FileName.PDF" because it is either not a supported file type or the file has been corrupt.".

FileToPDF(ReportName, strPath & ReportName & ".pdf","") --> Without specifying a return value, it gives an error. If the parenthesis are removed, Does not do anything. When tried to trap the return value into an integer, it returns a zero.

Thanks and Regards,

Pradeep Varma
1.<<<If select the RTF format, when we try opening the .pdf file, it gives an error "Acrobat could not open the "FileName.PDF" because it is either not a supported file type or the file has been corrupt.".>>>>> Format has to be entered as <<<acFormatRTF>>>>.....see below


DoCmd.OutputTo acOutputReport, ReportName,acFormatRTF, strPath & ReportName & ".pdf", False
_________________________________________________________________________
2.   <<<<Without specifying a return value, it gives an error>>>>

Try putting return value of 1 (boolean true) in up front.  Here are 3 different ways to try.

FileToPDF(ReportName, strPath & ReportName & ".pdf","") = True
or
FileToPDF(ReportName, strPath & ReportName & ".pdf","") = 1
or
FileToPDF(ReportName, strPath & ReportName & ".pdf",0) = 1
Hello puppydogbuddy,

DoCmd.OutputTo acOutputReport, ReportName,acFormatRTF, strPath & ReportName & ".pdf", False

Results in the same error "Acrobat could not open the "FileName.PDF" because it is either not a supported file type or the file has been corrupt"

objPDF.FileToPDF(Report, pdfPath & filename & ".pdf", "") = 1
                          Or
objPDF.FileToPDF(Report, pdfPath & filename & ".pdf", "") = True

                          Or
FileToPDF(ReportName, strPath & ReportName & ".pdf",0) = 1
 

reuturn a compile error:

Function call on the left hand side of assignment must return variant or object.

Thanks and Regards,

Pradeep Varma
Hello puppydogbuddy,

If we check the Acrobat Distiller API Reference, it clearly speicifes that it has to be a postscript file for Distiller to generate a PDF file. I have done some research in this regard and found that the print to file feature generates the postscript file in office applications. I could generate a postscript file using the following code:

Dim strReport as String,strPath as String, strFileName as String,objPDF as PdfDistiller

strPath="C:\Reports\"

strFileName="Y.pdf"

strReport="X"

Generate_PS strReport

Set objPDF = New PdfDistiller

objPDF.FileToPDF strPath & strReport & ".ps",strPath & strFileName & ".pdf",""



Sub Generate_PS(strReportName as String) ' Generates a post script file

     DoCmd.OpenReport strReportName,AcViewPreview
     SendKeys "%Fp%l~" & strpath & strReport & ".ps~",True
     DoCmd.Close  acReport,strReportName

End Sub

This has  created the PDF file  with the FileToPDF method.

Thanks for all your help,

Pradeep Varma
You are welcome Pradeep, glad you've got everything working.
Thanks for letting me know the final solution.