Printing a .pdf from VBA 1

[itsgoodtobeking]

I am treading in new water here. I need to be able to print a .pdf file automatically from MS Access VBA. I got the file to open and can view it, now I need some pointers on how to get this to print. Can anyone help me??

This is how I got the file to open. Where fName is the name of the file I am opening.

wbName = "C:\PMs\" & fName & ".pdf"
appName = "C:\Program Files\Adobe\Acrobat5.\Acrobat\Acrobat.exe"
whole = appName & " " & wbName
Call Shell(whole, vbMaximizedFocus)

Any suggestions would be greatly appreciated.

 

[GKProgrammer]

You should try using the api call ShellExecute, you can open or print any file you wish. There should be alot of info on the net about it but briefly here is an example:

-----------Global Declaration----------
Public Declare Function ShellExecute _
Lib "shell32.dll" _
Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long

-----------Global Declaration----------

-----------Printing File------------
Dim retval As Long

retval = ShellExecute(0, "print", ""C:\PMs\" & fName & ".pdf"", "", "", 0)

-----------Printing File------------

Hope this helps.

Regards,
gkprogrammer

 

[MyerK]

Why not 'Open' AvDOC and 'PrintPages' or 'PrintPagesSilent'?

I haven't play with AvDOC yet but I've started using PDDoc.

A bit of a pain getting info since Adobe is pricing documentation under the guise of ASN.

 

[Bullsandbears123]

try this code it works pretty well.....


Save A Report As PDF
faq703-2533


May 7, 2003

DCBBB deserves much of the credit for this addition.

This code was designed to save a file as a PDF file. It was not designed to have the newly saved file open after it was created. But this was happening for many people, including myself. I knew it was a registry entry somewhere, but due to the technology department at my office, I was unable to reinstall Adobe and find the fiendish registry entry. DCBBB did just that. He discovered that the registry entry HKCU\Software\Adobe\Acrobat PDFWriter\bExecViewer had been changed from the default of 0 (do not show after saving) to 1 (show after saving). So I have modified the code below to ALWAYS set the value of this registry key back to 0, thereby ensuring the file is always saved and never opened.

***********************************************************

It has been brought to my attention that this FAQ does not work on 9x products. This seems to be related to a registry structure difference. I have no access to a 9x platform, and therefore cannot correct this code to include 9x products. If anyone wants to send me the correction, I will be happy to include it and give them credit for their work.

***********************************************************

It is not a difficult task to save a file in PDF Format. This can be useful as it will keep all you formating, lines, boxes, and images. With just a small set of code and a simple call procedure, you can have any and all of your reports saved into the PDF format.

This code will:
- Determine you current default printer and save it as a variable
- Set your default printer to the Adobe Writer
- Save the file as PDF using the provided arguements
- Set your default printer back to what it was prior to this process

Important Note: You must have Adobe Writer (which you have to purchase) in order for this to work. Having Adobe Reader (Acrobat Reader), which can be downloaded from the Internet for free, will not work for you.

The first thing you must do is copy the following code into a module. Name the module basPDFSaver.

'**********************************************************

Public Const REG_SZ As Long = 1
Public Const REG_DWORD As Long = 4

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

Public Const ERROR_NONE = 0
Public Const ERROR_BADDB = 1
Public Const ERROR_BADKEY = 2
Public Const ERROR_CANTOPEN = 3
Public Const ERROR_CANTREAD = 4
Public Const ERROR_CANTWRITE = 5
Public Const ERROR_OUTOFMEMORY = 6
Public Const ERROR_ARENA_TRASHED = 7
Public Const ERROR_ACCESS_DENIED = 8
Public Const ERROR_INVALID_PARAMETERS = 87
Public Const ERROR_NO_MORE_ITEMS = 259

Public Const KEY_QUERY_VALUE = &H1
Public Const KEY_SET_VALUE = &H2
Public Const KEY_ALL_ACCESS = &H3F

Public Const REG_OPTION_NON_VOLATILE = 0

Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
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
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
Declare Function RegQueryValueExString Lib "advapi32.dll" Alias _
"RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
As String, lpcbData As Long) As Long
Declare Function RegQueryValueExLong Lib "advapi32.dll" Alias _
"RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
String, ByVal lpReserved As Long, lpType As Long, lpData As _
Long, lpcbData As Long) As Long
Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias _
"RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _
As Long, lpcbData As Long) As Long
Declare Function RegSetValueExString Lib "advapi32.dll" Alias _
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
ByVal Reserved As Long, ByVal dwType As Long, ByVal lpValue As _
String, ByVal cbData As Long) As Long
Declare Function RegSetValueExLong Lib "advapi32.dll" Alias _
"RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, _
ByVal cbData As Long) As Long

'**********************************************************

Public Sub SaveReportAsPDF(strReportName As String, strPath As String)

Dim strOldDefault As String

strOldDefault = QueryKey("Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device&quot

SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "Acrobat PDFWriter", REG_SZ

SetKeyValue "Software\Adobe\Acrobat PDFWriter", "PDFFilename", strPath, REG_SZ

SetKeyValue "Software\Adobe\Acrobat PDFWriter", "bExecViewer", 0, REG_SZ

DoCmd.OpenReport strReportName

SetKeyValue "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", strOldDefault, REG_SZ

End Sub

Public Function SetValueEx(ByVal hKey As Long, sValueName As String, _
lType As Long, vValue As Variant) As Long
Dim lValue As Long
Dim sValue As String
Select Case lType
Case REG_SZ
sValue = vValue & Chr$(0)
SetValueEx = RegSetValueExString(hKey, sValueName, 0&, _
lType, sValue, Len(sValue))
Case REG_DWORD
lValue = vValue
SetValueEx = RegSetValueExLong(hKey, sValueName, 0&, _
lType, lValue, 4)
End Select
End Function

Function QueryValueEx(ByVal lhKey As Long, ByVal szValueName As _
String, vValue As Variant) As Long
Dim cch As Long
Dim lrc As Long
Dim lType As Long
Dim lValue As Long
Dim sValue As String

On Error GoTo QueryValueExError

' Determine the size and type of data to be read
lrc = RegQueryValueExNULL(lhKey, szValueName, 0&, lType, 0&, cch)
If lrc <> ERROR_NONE Then Error 5

Select Case lType
' For strings
Case REG_SZ:
sValue = String(cch, 0)

lrc = RegQueryValueExString(lhKey, szValueName, 0&, lType, _
sValue, cch)
If lrc = ERROR_NONE Then
vValue = Left$(sValue, cch - 1)
Else
vValue = Empty
End If
' For DWORDS
Case REG_DWORD:
lrc = RegQueryValueExLong(lhKey, szValueName, 0&, lType, _
lValue, cch)
If lrc = ERROR_NONE Then vValue = lValue
Case Else
'all other data types not supported
lrc = -1
End Select

QueryValueExExit:
QueryValueEx = lrc
Exit Function

QueryValueExError:
Resume QueryValueExExit
End Function

Public Function CreateNewKey(sNewKeyName As String, lPredefinedKey As Long)

Dim hNewKey As Long ' Handle to the new key
Dim lRetVal As Long ' Result of the RegCreateKeyEx function

lRetVal = RegCreateKeyEx(lPredefinedKey, sNewKeyName, 0&, vbNullString, REG_OPTION_NON_VOLATILE, _
KEY_ALL_ACCESS, 0&, hNewKey, lRetVal)

RegCloseKey (hNewKey)

End Function

Public Function SetKeyValue(sKeyName As String, sValueName As String, vValueSetting As Variant, lValueType As Long)

Dim lRetVal As Long ' Result of the SetValueEx function
Dim hKey As Long ' Handle of open key

' Open the specified key
lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, KEY_SET_VALUE, hKey)

lRetVal = SetValueEx(hKey, sValueName, lValueType, vValueSetting)

RegCloseKey (hKey)

End Function

Public Function QueryKey(sKeyName As String, sValueName As String)

Dim lRetVal As Long ' Result of the API functions
Dim hKey As Long ' Handle of opened key
Dim vValue As Variant ' Setting of queried value

lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, KEY_QUERY_VALUE, hKey)

lRetVal = QueryValueEx(hKey, sValueName, vValue)

QueryKey = vValue

RegCloseKey (hKey)

End Function

'**********************************************************

That's all the code there is.....Now the setup.

Anywhere you want to save a report, you will use:

Call SaveReportAsPDF(&quot;Name of Report&quot;, &quot;Save Location&quot

The Name of Report is simply the name of the report in the database...if you use standard naming conventions, it will be something like rptInvoiceDetails, rptSummary, etc.

The Save Location is the full path and name of this output file....I would use something like C:\My Documents\Invoice Details.pdf, D:\New Database\Summary.pdf, etc

As a Final Note: Please be aware this code has NO ERROR CHECKING or graceful crash protection.....that is up to you to provde. There are no bells or whistles, such as asking if you really want to do this etc.....

And that's it. You are now saving your files in PDF format. Please feel free to contact me at wildmage@tampabay.rr.com with any questions.

 

[Rogoflap]

I have tried this and my report does not print to a pdf file. (I am on XP Pro and Office 2003) Adobe Acrobat standard (6.0)

What printer setting should the report have? Currently I have the report set up to use Adobe PDF. The code works fine except it still prompts me for the file name for the PDF file?

Thanks,

Rog

 

[JPMontreal]

Thanks,

You actually resolve a problem I post 15 days ago. Have a star.

Thanks.



Jean-Paul
Montreal
To send me E-Mail, remove “USELESSCODE”.
jp@USELESSCODEsolutionsvba.com