Is it possible to... (PDF)

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Is it possible to... (PDF)

Post by Egg 'n' Bacon »

I'm looking to print a report, from a record(using CutePDF) to a specified folder, with a file name derived from the current date & one field.

Is it possible to do this from a one-click button, giving an acknowledgement message?

We're using Access 2007, with about 2 machines with 2003.

TIA

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is it possible to... (PDF)

Post by HansV »

You can use code like this:

Code: Select all

Private Sub cmdPrint_Click()
  Const strRegKey = "HKEY_CURRENT_USER\Software\Custom PDF Printer\"
  Dim wsh As Object
  On Error GoTo ErrHandler
  Set wsh = CreateObject("WScript.Shell")
  ' Tell Cute PDF not to prompt for the filename
  wsh.regwrite strRegKey & "BypassSaveAs", "1"
  ' Specify filename
  wsh.regwrite strRegKey & "OutputFile", "C:\Test\Output.pdf"
  ' Now set the the printer to Cute PDF, and open the report
  ...
  ...
  MsgBox "Report printed to PDF", vbInformation

ExitHandler:
  On Error Resume Next
  ' Tell Cute PDF to prompt for the filename again
  wsh.regwrite strRegKey & "BypassSaveAs", "0"
  Set wsh = Nothing
  Exit Sub

ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub
Note: Access 2007 SP2 has print to PDF built in.
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Is it possible to... (PDF)

Post by Egg 'n' Bacon »

Currently at home, so I'll be trying it on Monday.

Cheers

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Is it possible to... (PDF)

Post by Egg 'n' Bacon »

Cheer Hans.

'fraid I'm not doing very well with this (lack of knowledge :scratch: ).

I'll start with the Registry part; the registry keys for CutePDF are as follows;
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\MenuOrder\Start Menu2\Programs\CutePDF
HKEY_CURRENT_USER\Software\Microsoft\Windows\ShellNoRoam\MUICache
HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Devices
HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts
Do I need to edit the Const strRegKey = to one of these values?

I can do the print code, but not how to select the relevant printer.

Re PDF in 2007 SP2, it hasn't been enabled on many PCs & it is the common denominator across the versions installed (I know, it's not how I'd manage IT systems either)

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is it possible to... (PDF)

Post by HansV »

You shouldn't use any of the registry keys that you mention.

The code I posted is based on info from CutePDF: Sample Code.

Perhaps "Custom PDF Printer" has to be replaced with the name of your PDF printer?

As an alternative, you could use the ReportToPDF code by Stephen Lebans (free): ReportToPDF. It works in Access 2000-2007, but you need to install the two DLLs on each PC.
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Is it possible to... (PDF)

Post by Egg 'n' Bacon »

Thank you sir.

I've got so far, but could you help me with the code for selecting the printer (and from there to print) please?

Here's the first section I have so far;

Code: Select all

  Const strRegKey = "HKEY_CURRENT_USER\Software\CutePDF Writer\"
  Dim wsh As Object
  On Error GoTo ErrHandler
  Set wsh = CreateObject("WScript.Shell")
  ' Tell Cute PDF not to prompt for the filename
  wsh.regwrite strRegKey & "BypassSaveAs", "1"
  ' Specify filename
  wsh.regwrite strRegKey & "OutputFile", "F:\ISO 18001\Risk Assessments\Obsolete Docs\Obsolete RAs\Output.pdf"
  ' Now set the the printer to Cute PDF, and open the report

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is it possible to... (PDF)

Post by HansV »

Try this:

' Set the printer
Application.Printer = Application.Printers("CutePDF Writer")
' Print the report
DoCmd.OpenReport ReportName:="rptMyReport"

Please use the name of the CutePDF printer exactly as it occurs in the list of printers, and replace rptMyReport with the name of the report to be printed.
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Is it possible to... (PDF)

Post by Egg 'n' Bacon »

Well I'm getting somewhere, but somethiing is not quite right. I've tried limiting the report to the current record, but I'm getting parameter boxes???

Also getting 'Save As' dialogue box, for filename & path.

Code used;

Code: Select all

  ' Now set the printer to Cute PDF, and open the report
  Application.Printer = Application.Printers("CutePDF Writer")
  
  ' Print the report
    stDocName = "rptSIR"
    DoCmd.OpenReport stDocName, acNormal, , WhereCondition:="ID=" & Me.ID
  MsgBox "Report printed to PDF", vbInformation

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is it possible to... (PDF)

Post by HansV »

What does the parameter prompt ask for? The value of ID, or ...?
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Is it possible to... (PDF)

Post by Egg 'n' Bacon »

Yes the ID, then another parameter box for the value entered

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is it possible to... (PDF)

Post by HansV »

That would seem to indicate that the ID field is not part of the record source of the report.
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Is it possible to... (PDF)

Post by Egg 'n' Bacon »

Ah! That made me go back & look again; I'd made a type AND used WhereCondition:="ID=" & Chr(34) & Me.ID & Chr(34) instead.

Still getting the Save As box though

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is it possible to... (PDF)

Post by HansV »

Perhaps someone who uses CutePDF can assist - I don't have it installed and have no need for it, so I can't test.
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Is it possible to... (PDF)

Post by Egg 'n' Bacon »

Well you did get me much further than if I were to do it myself.

Many thanks :thankyou:

Mark L
3StarLounger
Posts: 331
Joined: 11 Feb 2010, 03:55
Location: Land O Lakes, FL

Re: Is it possible to... (PDF)

Post by Mark L »

HansV wrote:You shouldn't use any of the registry keys that you mention.

The code I posted is based on info from CutePDF: Sample Code.

Perhaps "Custom PDF Printer" has to be replaced with the name of your PDF printer?

As an alternative, you could use the ReportToPDF code by Stephen Lebans (free): ReportToPDF. It works in Access 2000-2007, but you need to install the two DLLs on each PC.
I've used Stephen Lebans code for years, works great. However, I think it may not work with new Access2010. Supposedly it will have its own print to PDF capability, and will eliminate the "Snapshot" format; and Stephen's code first creates a .snp file and then converts that to a .pdf file. But I haven't seen it yet, so not sure.
Mark Liquorman
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is it possible to... (PDF)

Post by HansV »

For the moment, Stephen Lebans' solution is ideal because it works across Access 2000-2007. But when Access 2010 gets added to the mix, that won't work anymore, as you indicate. If you still have to support Access 2003 or earlier, a third-party PDF writer is the best solution. Once everybody has Access 2007 or later, you can use the native export to PDF feature.
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Is it possible to... (PDF)

Post by Egg 'n' Bacon »

If I had the choice, I'd use Stephen Leben's solution too, but the lilelihood of the company allowing the installation of the necessary DLL's is as close to zero as to make no difference.

As an aside, cand the default printer be determined (prior to running the PDF stuff), thereby allowing this to be reset on completion?

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Is it possible to... (PDF)

Post by HansV »

You don't really have to install DLLs (in the sense of registering them). Just copying them to the same folder as the database is sufficient.

You could use code like this for the printer:

Dim prt As Printer
' Store current printer in variable
Set prt = Application.Printer
' Set PDF printer
Set Application.Printer = Application.Printers("CutePDF Writer")
' Your stuff here
...
' Restore original printer
Set Application.Printer = prt
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Is it possible to... (PDF)

Post by Egg 'n' Bacon »

Ooh that was an easy one :thumbup:

Mark L
3StarLounger
Posts: 331
Joined: 11 Feb 2010, 03:55
Location: Land O Lakes, FL

Re: Is it possible to... (PDF)

Post by Mark L »

Egg 'n' Bacon wrote:If I had the choice, I'd use Stephen Leben's solution too, but the lilelihood of the company allowing the installation of the necessary DLL's is as close to zero as to make no difference.
I think you can put them in your \windows\sytem32 folder, but I prefer to do what Hans already suggested, put them in same folder as your frontend database. I have a .bat file I use to install/upgrade the frontend on a workstation. It creates a new folder (if needed), copies the database from the server (to make a local copy), and copies other files to same location (the PrintToPDF DLLs, some .WAV files, etc.).
Mark Liquorman
Land O Lakes, FL
see my website http://www.liquorman.net for Access Tips and Tricks, and for my Liquorman Utilities.