Create pdf from report using Cutepdf by means of VBA

[TheSmileyCoder]

I would like whenever a report is created by a user in my db to store the report as a pdf on a network location, without showing/troubling the user.

Anyone know if this can be done, and hopefully how?

Im currently using CutePDF, but any freeware pdf-writer could be an option.


I would need to be able to "feed" the cutePDF writer (or similar) with the desired filename.

[nico5038]

From the CutePDF FAQ:
- How to bypass Save As dialog box ,or make unattended installation?
The Custom Edition supports those features.

This custom edition will however cost some $$'s.

Basically you need an application that can be controlled by the SHELL() command.

Nic;o)

[ADezii]

I wrote an Off-the-Wall routine that should enable you to Auto Fill the Save As Dialog with a Unique Filename and Save it to the destination of your choice. You can change Lines 1, 8, and 11 to suite your specific needs.

Const conPATH_TO_PDF As String = "C:\Stuff\"
Dim strPathToPDF As String
Dim strReportName As String

'Changes the Default Printer, then resets it to the original Printer.
'This will only work if the report is set up to Print to the Default
'Printer
Set Application.Printer = Application.Printers("PDF Converter")

'Report Name assign to Variable
strReportName = "rptPDF"

'Make the *.PDF File Unique
strPathToPDF = conPATH_TO_PDF & strReportName & Format$(Now(), "yyyymmddhhmmss") & ".pdf"

DoCmd.OpenReport strReportName, acViewNormal, , , acWindowNormal

'Fill in the Absolute/Unique Path in the Save As Dialog
SendKeys strPathToPDF, True

'TAB to the Save Command Button
SendKeys "{TAB 2}", True

'Simulate Clicking the Save Key
SendKeys "{ENTER}", True
SendKeys "{ENTER}", True
  
'Reset the Default Printer back to the way it was
Set Application.Printer = Nothing

[TheSmileyCoder]

NICE!

Simple, but impressive. Looking forward to testing it.

[ADezii]

It was actually functional on my end, I guess anything is possible! (LOL)

P.S. - The strange part is that the first

SendKeys "{ENTER}", True

appears to be ignored.

[nico5038]

I'm a bit reluctant to use the SendKeys, as they might get interrupted / send to another application when the user clicks somewhere on the screen...
There's never a 100% guarantee that it's finished correctly, so you need to make sure your application offers a "re-try".

Nic;o)

[ADezii]

Hello Nico, I would imagine that the chances of a User clicking somewhere else on the Screen after clicking a Command Button to initiate code execution would be infinitesimal, but to be absolutely sure, you can suppress all forms of Keyboard/Mouse Input prior to executing the code, then enable Input again post execution. Refer to Code Lines 4 and 32.

P.S. - Error Checking intentionally omitted.

Public Declare Function BlockInput Lib "USER32.DLL" (ByVal fBlockIt As Long) As Long

Const conPATH_TO_PDF As String = "C:\Stuff\"

Dim strPathToPDF As String
Dim strReportName As String

BlockInput True

'Changes the Default Printer, then resets it to the original Printer.
'This will only work if the report is set up to Print to the Default
'Printer
Set Application.Printer = Application.Printers("PDF Converter")

'Report Name assign to Variable
strReportName = "rptPDF"

'Make the *.PDF File Unique
strPathToPDF = conPATH_TO_PDF & strReportName & Format$(Now(), "yyyymmddhhmmss") & ".pdf"

DoCmd.OpenReport strReportName, acViewNormal, , , acWindowNormal

'Fill in the Absolute/Unique Path in the Save As Dialog
SendKeys strPathToPDF, True

'TAB to the Save Command Button
SendKeys "{TAB 2}", True

'Simulate Clicking the Save Key
SendKeys "{ENTER}", True
SendKeys "{ENTER}", True
  
'Reset the Default Printer back to the way it was
Set Application.Printer = Nothing

BlockInput False

[TheSmileyCoder]

Well I use a DB that creates (and formats) 100 page long documents (Requirement specifications) , as well as doing crossreferences , image formatting, and creating a Table of contents, so it takes around 30 seconds for my code to generate the report. I imagine sending it to the CutePDF writer is another 5 seconds.

What would happen if an error occured while you have blocked input? Would you be "stuck"?
I presume you could add a error handler like:

Public Sub GenereatePDF()
On error goto Err_Handler
BlockInput True

/Code code code


Err_Handler:
BlockInput False

[ADezii]

Hello TheSmileyOne, you answered your own question. Please be advised that this approach is a little 'HAIRY' since it is usually never a good idea to block all forms of Input into an Application. I do feel, however, with careful coding and a little User Training, it can be practical. The decision is all yours.

Private Sub Command13_Click()
On Error GoTo Err_Command13_Click:
Const conPATH_TO_PDF As String = "C:\Stuff\"
Dim strPathToPDF As String
Dim strReportName As String

BlockInput True

'Changes the Default Printer, then resets it to the original Printer.
'This will only work if the report is set up to Print to the Default
'Printer
Set Application.Printer = Application.Printers("PDF Converter")

'Report Name assign to Variable
strReportName = "rptPDF"

'Make the *.PDF File Unique
strPathToPDF = conPATH_TO_PDF & strReportName & Format$(Now(), "yyyymmddhhmmss") & ".pdf"

DoCmd.OpenReport strReportName, acViewNormal, , , acWindowNormal

'Fill in the Absolute/Unique Path in the Save As Dialog
SendKeys strPathToPDF, True

'TAB to the Save Command Button
SendKeys "{TAB 2}", True

'Simulate Clicking the Save Key
SendKeys "{ENTER}", True
SendKeys "{ENTER}", True
  
'Reset the Default Printer back to the way it was
Set Application.Printer = Nothing

BlockInput False

Exit_Command13_Click:
  Exit Sub

Err_Command13_Click:
  BlockInput False
    MsgBox Err.Description, vbExclamation, "Error in Command13_Click()"
      Resume Exit_Command13_Click
End Sub