[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7834
  • Last Modified:

Quickest way to print Access report to PDF

Hello fellow experts

I now have Adobe Acrobat 7 Try-Out, and an Access database with references set to 'Adobe Acrobat 7.0 Type Library' and 'AdobePDFMakerForOffice'.

What is the quickest way using VBA to print an Access report out to a specific drive/path/file.pdf?

TIA
-Jim
0
Jim Horn
Asked:
Jim Horn
  • 13
  • 8
  • 6
1 Solution
 
Karl Heinz KremerCommented:
Use this document: http://partners.adobe.com/public/developer/en/acrobat/sdk/pdf/pdf_creation_apis_and_specs/DistillerAPIReference.pdf
This describes (bottom of page 20) how to set the destination path and filename by setting a registry key. This key will be active for the next document that is printed to the Adobe PDF printer.

The complete process would look like this:

set the registry key
print to the Adobe PDF printer
open the PDF file with the code that you already have so that you can modify it.
0
 
Karl Heinz KremerCommented:
BTW: This also works with Acrobat 6, but was not documented for that version. It does not work with any older Acrobat version.
0
 
Karl Heinz KremerCommented:
One more thing: The PDFMaker is not a supported developer tool. It's not documented. And depending on how you interpret certain sections in the EULA/developer FAQ this could mean that you don't have a license to use it.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Working...

I am able to set the registry key
HKEY_CURRENT_USER\Software\Adobe\Acrobat Distiller\PrinterJobControl\applications to
'C:\\Program Files\Microsoft Office\Office10\MSACCESS.EXE' = 'C:\path\file.pdf'"

However in Access VBA when I execute this line, which is supposed to send a report straight to the printer, it still get the Windows common file dialog with a 'Save PDF File As' caption.  
DoCmd.OpenReport "My report name", acViewNormal

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
(Not sure if this helps, but...)

Before the Windows Common File dialog opens, I get a 'Printing' dialog that displays 'Now Printing 'My Report name' to the Adobe PDF on My Documents\*.pdf.
0
 
Karl Heinz KremerCommented:
Is your key still there after you print? Acrobat is supposed to remove it once it's used it.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Yes it is, and this key appeared after printing
....\PrinterJobControl\LastPdfPortFolder - MSACCESS.EXE
C:\Documents and Settings\Jim Horn\My Documents            '(I just accepted the default path in the WCFO dialog)

Also, I changed the single-quote marks I had typed earler to double-quote marks using chr(34).. same result
0
 
Karl Heinz KremerCommented:
You have one double-back-slash in your path. I would try to make this a single back-slash.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Same result.

Also, the DistillerAPIReference.pdf, page 20 example you pointed me to uses double-back-slashes everywhere.  I tried this with both double and single.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
It works!

On the top of page 20 there are instructions for changing the printer's 'Adobe PDF Output Folder'.  You have to change this to a path other than 'Prompt for Adobe PDF Filename' to remove the prompt.

I was able to get the file to programmatically print, however instead of
Also, uncheck the 'View Adobe PDF results' checkbox in the same dialog, as if this is checked Adobe Acrobat Reader will open and load the file.

With the above two, Access VBA created the .pdf as path\My Report Name.pdf, and I was able to do a fso.MoveFile command to rename it to whatever I wished.

Also, I was never able to get the registry entry to perform as advertised.  After implimenting all of the above, I commented out the registry editing code, and it still created and renamed .pdf files without any prompts.

Thanks for pointing me in the right direction.
-Jim
0
 
Karl Heinz KremerCommented:
Is the path to your MSACCESS.EXE executable correct? Mine is stored in C:\Program Files\Microsoft Office\Office and not in Office10.
0
 
Karl Heinz KremerCommented:
BTW: You don't have to change your program to test this. If you use regedit to change the key, and then just do a manual print from within Access to the Adobe PDF printer, you should be able to see that the specified file gets created. It works for me.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Yep.  C:\Program Files\Microsoft Office\Office\Office10 = XP.   I think Office11 = 2003.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
I'm using SetKeyValue (http://support.microsoft.com/default.aspx?scid=kb;en-us;145679) as a function call to change the key.

Doing it manually is not an option for me, as the process I'm building has a loop that will execute around 200x.

I'll experiment with doing this manually, just to see if it works on my pc.
0
 
Karl Heinz KremerCommented:
I didn't suggest that you do this manually for your production environment. It's just easier to change a value in regedit while you are experimenting.
0
 
Karl Heinz KremerCommented:
Are you sure that you tried it with all single backslashes? The example you gave (C:\\Program Files\Microsoft Office\Office10\MSACCESS.EXE) has one double-backslash right after the C:. If I try the path as you've specified it, Acrobat will prompt for a filename, for a path with only single backslashes it works. The double-backslashes you found on page 20 are in the registry file that you would import into the registry editor. In this file format, it's necessary to escape all backslashes (and other special characters) with a '\'. YOu would never use this when you enter the key in the registry editor.
0
 
stocker7570Commented:
Hello.  I'm afraid I'm having the same difficulty with it still prompting for a file name from VBA code in Access 2003.  The \PrinterJobControl\application key is set to 'D:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE' = 'c:\temp.pdf' (I've also tried "D:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" = "c:\temp.pdf"  [double instead of single quotes]).  I've got Acrobat 6.0 Pro.  It appears to be ignoring the registry key no matter what I try and I'd rather not set the printer defaults, although that is always an option.  Do I have the wrong syntax? Might I need a patch?  Any ideas?  Thanks in advance for any assistance.
0
 
Karl Heinz KremerCommented:
Try it with regedit, and don't use any quotes. Does this work?
0
 
stocker7570Commented:
Thank you for your quick response.  I'm afraid it doesn't like anything.  I tried the following combinations:

D:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE = c:\temp.pdf (I doubt this is an option because of the spaces)
'D:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE' = c:\temp.pdf
"D:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" = c:\temp.pdf
'D:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE' = 'c:\temp.pdf'
"D:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" = "c:\temp.pdf"

Interestingly, v6 Pro doesn't have the "Adobe PDF Output Folder" field as described at the top of page 20 for the Adobe PDF Settings tab under Printing Preferences.  So, I can't use that option either.  I'm willing to upgrade to v7.  Do you think that will solve it?

Again, thanks for your help.
0
 
Karl Heinz KremerCommented:
You can download the 30 day eval version of Acrobat 7 and give it a try.
0
 
Karl Heinz KremerCommented:
Write the following lines into a text file with WordPad, save it as distiller.reg and double-click on it.

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Adobe\Acrobat Distiller\PrinterJobControl]
"D:\\Program Files\\Microsoft Office\\OFFICE11\\MSACCESS.EXE" = "c:\\temp.pdf"

This will create the entry for your configuration. Does this work?
0
 
stocker7570Commented:
Sorry, I forgot to mention I already tried that with no effect.  I also upgraded to v6.0.1--no change.  I'm now downloading v7 trial.  I'll let you know.  Thanks.
0
 
stocker7570Commented:
OK, so v7 didn't work either.  It also ignored the registry entry.  It's a shame--that would have worked great.

So, I used this method:

http://www.businessandcomputers.com/Programmers_Main.aspx  (#12)

Instructions:
http://www.businessandcomputers.com/ProgramsStuff/AdobeAcrobat_VBCode.pdf

The sample Access database gives options for the Distiller or PDFWriter.  However, I only got it to work with Acrobat 5's Distiller as the web site suggests.  The PDFWriter printer doesn't work because my laptop doesn't have an LPT1, which was required by v5.  So, I set up the Distiller's printer port to point to a designated queue directory and name the file using the registry entry:

HKCU\Software\Adobe\Acrobat PDFWriter\PDFFileName

which is set through code.  You can't use the full path, just the file name.  The file will get written to the queue, after which, it can be used for whatever.

I would have liked to keep v6 but I didn't want to have to set the printing defaults to not prompt for a file name.  While that would have worked for the automation, it wouldn't have been convenient for manually creating a PDF from, say, a scanner.

Thanks for all the great ideas.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
FOLLOW-UP:  I just discovered that whatever is in the Report's .Caption property is what will be the file name.
0
 
stocker7570Commented:
You are correct, Jim.  And I can't explain it, but the registry entry (HKCU\Software\Adobe\Acrobat PDFWriter\PDFFileName), that I believe I had working at one point, no longer works with v5 Distiller so I broke down and set the printing defaults to not prompt for a filename and to not display the resulting PDF.  I did what you suggested, Jim, and simply renamed the file after it was printed to the output directory (port).  I suppose I could now use v6 but it works fine this way so I'm not going to mess with it anymore.
0
 
Karl Heinz KremerCommented:
PDFWriter is _NOT_ Distiller. There were two ways to create PDF documents with Acrobat versions up to 5: Distiller and PDFWriter. Distiller is a PostScript to PDF converter which is used as the backend for the Distiller printer, which first uses a PostScript driver to create a PS file, and then Distiller is called to convert this PS file to a high quality PDF file. PDFWriter is a printer driver that converts GDI drawing commands directly to PDF. This method did create low(er) quality PDF files. Adobe decided to no longer install PDFWriter with the default installation of Acrobat 5, but it's still on the CD and can be installed with a custom installation. Starting with Acrobat 6, PDFWriter was no longer distributed with Acrobat. If you want to use the key you quoted in your lats comment, you have to print to the PDFWriter printer and not to the Distiller printer.
0
 
stocker7570Commented:
Thanks for your comments.  However, I am aware of the distinction between v5 Distiller and PDFWriter but I was fairly certain that printing to the Distiller printer used the registry entry above to take it's file name.  Like I said, it appeared to work when testing, then I couldn't get it to use the file name from the registry entry again once my automation was completed.  Granted, it did not make sense that the registry entry referred to PDFWriter, but, like I said, it appeared to work.  Obviously, the file name was coming from somewhere else, was the same name and I didn't catch it at the time.  And, like I mentioned above, v5 PDFWriter doesn't work on a machine without an LPT1 port so I can't even use that printer when manually printing to PDF.  Thus, I can't use the registry key--in either v5 or v6.  The method of printing to the same file name every time and then renaming and moving the file is not very elegant but acceptable.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 13
  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now