Printout save to PDF

[duncanmcl]

I have code to print out to PDF, I'm able to force a unique file name and I'm sitting on the save to pdf Windows window. I'm tring to programatically tab..tab and enter to complete the save, then next in a loop. I have tried the sendkeys method without success. It seems control/focus has passed to the save window and sendkeys are not working. I can manually tab..tab and enter to complete this need...but want to control it via VBA. I'm in Access 2003 under Windows XP. Any suggestions, thanks in advance.

 

[boblarson]

What code are you using to save to PDF (as 2003 doesn't have that functionality built-in)?

 

[boblarson]

Oh, wait - if you are using DoCmd.Printout then you won't be able to do what you want. You will need to have separate code to print to PDF so you can set the path yourself.

You can use Stephen Lebans' code if you want but that requires some dlls to be included on the machine. Or you can use the code I'm using for our stuff at work but you do have to put this code in a STANDARD MODULE (and you have to have an Adobe Acrobat printer installed):

'===========================================================
' Code begins here
'
' The function to call is RunReportAsPDF
'
' It requires 2 parameters:  the Access Report to run
'                            the PDF file name
'
' Enjoy!
'
' Eric Provencher
'===========================================================


Private Declare Sub CopyMemory Lib "kernel32" _
              Alias "RtlMoveMemory" (dest As Any, _
                                     source As Any, _
                                     ByVal numBytes As Long)

Private Declare Function RegOpenKeyEx Lib "advapi32.dll" _
                  Alias "RegOpenKeyExA" (ByVal hKey As Long, _
                                         ByVal lpSubKey As String, _
                                         ByVal ulOptions As Long, _
                                         ByVal samDesired As Long, _
                                         phkResult As Long) As Long

Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long

Private Declare Function RegCreateKeyEx Lib "advapi32.dll" _
                   Alias "RegCreateKeyExA" (ByVal hKey As Long, _
                                            ByVal lpSubKey As String, _
                                            ByVal Reserved As Long, _
                                            ByVal lpClass As String, _
                                            ByVal dwOptions As Long, _
                                            ByVal samDesired As Long, _
                                            ByVal lpSecurityAttributes As Long, _
                                            phkResult As Long, _
                                            lpdwDisposition As Long) As Long

Private Declare Function RegQueryValueEx Lib "advapi32.dll" _
                   Alias "RegQueryValueExA" (ByVal hKey As Long, _
                                             ByVal lpValueName As String, _
                                             ByVal lpReserved As Long, _
                                             lpType As Long, _
                                             lpData As Any, _
                                             lpcbData As Long) As Long

Private Declare Function RegSetValueEx Lib "advapi32.dll" _
                   Alias "RegSetValueExA" (ByVal hKey As Long, _
                                           ByVal lpValueName As String, _
                                           ByVal Reserved As Long, _
                                           ByVal dwType As Long, _
                                           lpData As Any, _
                                           ByVal cbData As Long) As Long

Private Declare Function apiFindExecutable Lib "shell32.dll" _
                  Alias "FindExecutableA" (ByVal lpFile As String, _
                                           ByVal lpDirectory As String, _
                                           ByVal lpResult As String) As Long

Const REG_SZ = 1
Const REG_EXPAND_SZ = 2
Const REG_BINARY = 3
Const REG_DWORD = 4
Const REG_MULTI_SZ = 7
Const ERROR_MORE_DATA = 234

Public Const HKEY_CLASSES_ROOT = &H80000000
Public Const HKEY_CURRENT_USER = &H80000001
Public Const HKEY_LOCAL_MACHINE = &H80000002

Const KEY_ALL_ACCESS = &HF003F
Const REG_OPTION_NON_VOLATILE = 0

Const KEY_READ = &H20019  ' ((READ_CONTROL Or KEY_QUERY_VALUE Or
                          ' KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY) And (Not
                          ' SYNCHRONIZE))

Const KEY_WRITE = &H20006  '((STANDARD_RIGHTS_WRITE Or KEY_SET_VALUE Or
                           ' KEY_CREATE_SUB_KEY) And (Not SYNCHRONIZE))

Public Function RunReportAsPDF(prmRptName As String, _
                               prmPdfName As String) As Boolean

' Returns TRUE if a PDF file has been created

Dim AdobeDevice As String
Dim strDefaultPrinter As String

'Find the Acrobat PDF device

AdobeDevice = GetRegistryValue(HKEY_CURRENT_USER, _
                               "Software\Microsoft\WIndows NT\CurrentVersion\Devices", _
                               "Adobe PDF")

If AdobeDevice = "" Then    ' The device was not found
    MsgBox "You must install Acrobat Writer before using this feature"
    RunReportAsPDF = False
    Exit Function
End If

' get current default printer.
strDefaultPrinter = Application.Printer.DeviceName

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

'Create the Registry Key where Acrobat looks for a file name
CreateNewRegistryKey HKEY_CURRENT_USER, _
                     "Software\Adobe\Acrobat Distiller\PrinterJobControl"

'Put the output filename where Acrobat could find it
SetRegistryValue HKEY_CURRENT_USER, _
                 "Software\Adobe\Acrobat Distiller\PrinterJobControl", _
                 Find_Exe_Name(CurrentDb.Name, CurrentDb.Name), _
                 prmPdfName

On Error GoTo Err_handler

DoCmd.OpenReport prmRptName, acViewNormal   'Run the report

While Len(Dir(prmPdfName)) = 0              ' Wait for the PDF to actually exist
    DoEvents
Wend

RunReportAsPDF = True       ' Mission accomplished!

Normal_Exit:

Set Application.Printer = Application.Printers(strDefaultPrinter)   ' Restore default printer

On Error GoTo 0

Exit Function

Err_handler:

If Err.Number = 2501 Then       ' The report did not run properly (ex NO DATA)
    RunReportAsPDF = False
    Resume Normal_Exit
Else
    RunReportAsPDF = False      ' The report did not run properly (anything else!)
    MsgBox "Unexpected error #" & Err.Number & " - " & Err.Description
    Resume Normal_Exit
End If

End Function

 

[duncanmcl]

thanks for your reply. In Access 2003, using VBA, I filter a report, update the reports caption, and print using printout...this way the unique report name for PDF is set and populates the save as windows/adobe window....I have tried to use sendkeys to tab..tab and enter..ie save...then close report...next loop to next filtered report and repeat. My issue is to auto save. It seems control has been passed to Adobe and sendkeys may not work their...maybe thay have their own commands...or this save window can be by-passed in VBA?

 

[boblarson]

thanks for your reply. In Access 2003, using VBA, I filter a report, update the reports caption, and print using printout...this way the unique report name for PDF is set and populates the save as windows/adobe window....I have tried to use sendkeys to tab..tab and enter..ie save...then close report...next loop to next filtered report and repeat. My issue is to auto save. It seems control has been passed to Adobe and sendkeys may not work their...maybe thay have their own commands...or this save window can be by-passed in VBA?

I already said - the way you are asking to do it will not work. You will need to go about it in another way if you want to just set the path and print.

And you should avoid SendKeys like the plague as they are not reliable.

 

[duncanmcl]

I tried the similar logic of Todd Benson...could not get it to work
Name: Create PDF from MS Access Report
'
' Description:On a machine where the Ado
' be PDFWriter is installed, the current p
' rinter is swapped out with the PDFWriter
' and the PDF file is created. The origina
' l printer is then restored.
' By: Todd Benson
'
'
' Inputs:rptName = Microsoft Access repo
' rt name you want to create pdf from. sPD
' FPath = the directory path where you wan
' t to create the pdf file (ex. - "c:\data
' \"). sPDFName = the name of the pdf file
' you are wanting to create (ex. - "file00
' 1.pdf").

 

[boblarson]

Well, your last post wasn't at all helpful. You may have tried something but just posting a very partial bit of it and saying you couldn't get it to work is like me going to the doctor and saying, "Doc, I have a pain and I tried to get rid of it but it didn't work." Do you suppose the doctor might want to know where the pain was and what exactly I'd done to try to get rid of it?

 

[dfenton]

You might want to look into PDFCreator, which is automatable from Access, and which I have clients using in their Access 2003 applications.

 

[raghuprabhu]

I found the sample database and dlls on Steven Lebans site and modified it and it is working well.

I have attached it below. Hope it will be of help to you.

Cheers
Raghu Prabhu
Melbourne, Oz

 

[ghudson]

Bob, I just tried using your RunReportAsPDF code but I get a Sub or function not defined error on the AdobeDevice = GetRegistryValue(HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", "Adobe PDF") line and the GetRegistryValue is high lited. Is this a builtin function that Access 2007 should recognize or am I missing the acutal GetRegistryValue() function in the code? I tried googling but could not find anything else. I can find the value [winspool,Ne06:] in the registry so I know it exists. Thanks!

 

[PNGBill]

Bob, I just tried using your RunReportAsPDF code but I get a Sub or function not defined error on the AdobeDevice = GetRegistryValue(HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", "Adobe PDF") line and the GetRegistryValue is high lited. Is this a builtin function that Access 2007 should recognize or am I missing the acutal GetRegistryValue() function in the code? I tried googling but could not find anything else. I can find the value [winspool,Ne06:] in the registry so I know it exists. Thanks!

ghudson,

Did you resolve this issue with the registry value??
I have the same issue with windows xp pro and access 2000

 

[ghudson]

ghudson,

Did you resolve this issue with the registry value??
I have the same issue with windows xp pro and access 2000

No I have not. I have not seen Bob posting lately either.

It stinks that the built-in PDF function in Access 2007 crashes whenever I try to output reports larger than 250 pages. The DoCmd.OutputTo acOutputReport, "SRP Combo Report", acFormatPDF method is very slow compared to using the Adobe PDF writer and the acFormatPDF method also creates larger files (file size) compared to Adobe PDF. We all have Adobe Acrobat Distiller but I cannot find any working code that will allow me to silently and without user intervention create a PDF file to a custom location and file name on the fly when the code is called.

 

[PNGBill]

Yes, it seems like a simple task.
We use 2000 and have adobe acrobat 5.

I will try re installing acrobat later today but am not hopeful this will help.
Thanks.

 

[PNGBill]

Bob, If you catch this post, any advice on how to get printing to .pdf working for access 2000 where acrobat writer is installed ??

 

[boblarson]

Not sure Bill. The code I gave works with the Adobe PDF printer (not PDF Writer or Distiller). What version of Acrobat are you using?

 

[PNGBill]

Version 5.

When I go Start, printers and Faxes I can only see Acrobat Distiller and Acrobat PDFWriter.

You are saying there is also Acrobat Printer that I need to have.

 

[boblarson]

Version 5.

When I go Start, printers and Faxes I can only see Acrobat Distiller and Acrobat PDFWriter.

You are saying there is also Acrobat Printer that I need to have.

Well, we're using 5 as well and this is what I have for printers and the code I use references the bottom one.

 

[PNGBill]

I see it says PDF Converter.

I will check my adobe disc - Thanks Bob

 

[PNGBill]

Bob, On the Acrobat Forum advice is that pdf Writer became pdf Printer on or just after acrobat 5.

Our version is 5.0

Is your version 5.>0 ?

Of course the Adobe web site doesn't have anything to hand on version 5.

Very sad when good software that still works isn't supported.

I can understand the difficulty but have you seen the price of Acrobat software ?? I think it is twice the price of office Professional.

 

[ghudson]

Bob, I also have the Adobe PDF printer for I am using Adobe Acrobat 8 Professional. When I try to run your RunReportAsPDF code I get a Sub or function not defined error on the AdobeDevice = GetRegistryValue(HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", "Adobe PDF") line and the GetRegistryValue is hi-lited. Is this a builtin function that Access 2007 should recognize or am I missing the acutal GetRegistryValue() function in the code? I tried googling but could not find anything else to help. I can find the value [winspool,Ne06:] in the registry so I know it exists. Thanks!