Creating PDF In Access 6

[yukonjack]

Here is what I need to do. In an Access form I need to convert documents from word to a secured pdf document all in one step. I have found some pdf convertors and I have adobe distiller as well, I need to know how to call it in. The second step is I need to save the document to a folder that is specified from a drop down in that record. For example the drop down might have communication as a choice and that will have to go to a specific folder named communication. Any help will be greatly appreciated.

Thanks

 

[Shortie]

I have use a tool called Ghostscript to convert a document to PDF. I setup a printer that outputs a postscript file to say c:\temp and then code a command to call the ghostscript. This reads the postscript file and converts it to PDF. Cheapest way I know. It works well. The only drawback is you can only convert one file at a time, because the printer outputs to c:\temp\file.ps each time.

I'm not at work now, otherwise I could send you my code examples. If you want to know more about this method, let me know.



On On
Johnathan Perry

 

[BSman]

If you have set up Acrobat distiller as the default printer before you print the Access document (which is not in Word format, by the way, since Access will only output to an RTF format document), then you will be asked for the folder and file name as part of the distiller dialog.

If these are truly Word documents, why not just print them from Word, except print them to the Distiller "printer"?

 

[SeeThru]

Hi Shortie

I'm really interested in using the GhostScrips - I have checked out the site, but found it heavy going as I have never had reason to play around with postscript before - further information much appreciated!

 

[yukonjack]

BSman and Shortie,

Shortie send me the code sounds like I can use it. I found software that is called WIN2PDF that offers the security I need. The system I have set-up is file management and project management system and unfortunately they want everything done within that system including file conversion and automate the save as feature to save to a folder specified by a drop down. I think your way is much simpler.

 

[dmtelf]

Shortie,

I'd be fascinated to see working Access/Ghostscript code examples so I can experiment and learn more. Could you please email it to me (misraelsohn@pentland.com)?

Many thanks in advance!

Matt

 

[moolie]

I used PDF995 for the same problem. Set the default printer to PDF995(basically a streamlined distiller) and then print the document/report. This tricks Access into thinking it's printing to a printer, but it's printing to the pdf generator. I have code for this as well.

 

[Dan8376]

To automate PDF creation using Adobe Writer you need to do the following steps.

(All of these steps below can be automated with VB)

1. Change your default printer to Adobe Writer

2. Open your WIN.ini file, there should be a referece to the "Acrobat PDFWriter", set the PDFFileName= equal to your network location file name and file extention. For example PDFFileName=C:\My Documents\Test.pdf (This step bypasses the dialog box that pops up when you try and create a PDF file with Adobe Writer.)

2a. On some PCs you have to repeat this step for the __pdf.ini file even if its not viewable. This file will be in the same directory as your Win.ini file.

3. Print your document, report, spreadsheet, etc.

4. Set your ini files and default printer back to their previous settings.


By doing these steps you can create PDF documents directly out of Access or any other application.

The only downfall is that printing with Adobe Writer does not provide the same quality as Adobe Distiller.

Dan

 

[yukonjack]

Hey moolie send me the code and the sub-routine. my email is buckeye19722@cs.com. I can print to a distiller. Hey Dan great advise I will try using both. Thanks

 

[yukonjack]

Dan,

Do you have the VB code to do this?

 

[cherrielwy]

moolie,
Hi, can u send me the code.I have download the pdf995 already.My email is cherrie_loo@hotmail.com

Thank You

 

[blackbri]

Could someone please post the code here to do this. I am stuck with automating the filename entry. My code will not enter this data so that it will save it.

Any help would be appreciated.

 

[TonyVBACoder]

The easiest way to Create a PDF file from an Access Report is to use your Windows Registry to manipulate your Default Printer (See sample code after the 5 Steps below):
1) Read in your current default Printer and store it in a variable
2) Change the default printer to the PDF Writer in your Windows Registry
3) Set the value for PDFFileName in the registry so that it will print the Report without having the PDF Creation dialog box from appearing
4) Open the Report so it will print it as a PDF
5) Change the Default Printer from PDF Writer back to the default printer with the variable you used in Step #1

Below is my Subroutine to Print an Access report as a PDF. It takes 3 arguments. 1) The Access Report (sReportName) to convert to PDF 2) The PDF file name you would like to use for the convertd PDF (sPDFFileName) 3) The full path to the new PDF File - this will be stored in the Registry (sFileAttachment):


Public Sub PrintReportToPDF(sReportName As String, sPDFFileName As String, sFileAttachment As String)
Dim sMyDefPrinter As String

' *** PDF Export ***

' Read the current default printer and save the value - we will need this later when we reset the Default Printer
sMyDefPrinter = dhReadRegistry(HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device")

' Change the default printer to the PDF Writer
If Not dhWriteRegistry(HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "Acrobat PDFWriter") Then
GoTo Err_RunReport
End If

' Setting the value for PDFFileName in the registry Prints the Report without the dialog box from appearing
If Not dhWriteRegistry(HKEY_CURRENT_USER, "Software\Adobe\Acrobat PDFWriter", "PDFFileName", sFileAttachment) Then
GoTo Err_RunReport
End If

' Open the Report so it will print it
DoCmd.OpenReport sReportName, acViewNormal

' Change the Printer from PDF Writer back to the default printer
dhWriteRegistry HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", sMyDefPrinter

Exit Sub

Err_RunReport:
' Restore default printer in the Registry
dhWriteRegistry HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", sMyDefPrinter

MsgBox Err.Description, vbCritical, "Error Creating PDF File"

End Sub


The following functions are required to read/write to the Registry. I put the following code into a separate module named "WindowsRegistry":

Option Compare Database
Option Explicit

Type FILETIME
dwLowDateTime As Long
dwHighDateTime As Long
End Type

Type SECURITY_ATTRIBUTES
nLength As Long
lpSecurityDescriptor As Long
bInheritHandle As Boolean
End Type

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" _
(ByVal hKey As Long) As Long
Private Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias "RegCreateKeyExA" _
(ByVal hKey As Long, ByVal lpSubKey As String, _
ByVal ulReserved As Long, ByVal lpClass As String, _
ByVal dwOptions As Long, ByVal samDesired As Long, _
lpSecurityAttributes As Any, 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 dwReserved 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 dwReserved As Long, ByVal dwType As Long, _
lpData As Any, ByVal cbData As Long) As Long

Private Declare Function RegEnumKeyEx Lib "advapi32.dll" Alias "RegEnumKeyExA" _
(ByVal hKey As Long, ByVal dwIndex As Long, _
ByVal lpName As String, lpcbName As Long, _
lpReserved As Long, ByVal lpClass As String, _
lpcbClass As Long, lpftLastWriteTime As FILETIME) As Long

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

' Registry constants
Global Const dhcSuccess = 0
Global Const dhcRegMaxDataSize = 2048
Global Const dhcRegNone = 0
Global Const dhcRegSz = 1
Global Const dhcRegExpandSz = 2
Global Const dhcRegBinary = 3
Global Const dhcRegDWord = 4
Global Const dhcRegDWordLittleEndian = 4
Global Const dhcRegDWordBigEndian = 5
Global Const dhcRegLink = 6
Global Const dhcRegMultiSz = 7
Global Const dhcRegResourceList = 8
Global Const dhcRegFullResourceDescriptor = 9
Global Const dhcRegResourceRequirementsList = 10
Global Const dhcRegOptionReserved = 0
Global Const dhcRegOptionNonVolatile = 0
Global Const dhcRegOptionVolatile = 1
Global Const dhcRegOptionCreateLink = 2
Global Const dhcRegOptionBackupRestore = 4
Global Const dhcReadControl = &H20000
Global Const dhcKeyQueryValue = &H1
Global Const dhcKeySetValue = &H2
Global Const dhcKeyCreateSubKey = &H4
Global Const dhcKeyEnumerateSubKeys = &H8
Global Const dhcKeyNotify = &H10
Global Const dhcKeyCreateLink = &H20
Global Const dhcKeyRead = dhcKeyQueryValue + dhcKeyEnumerateSubKeys + _
dhcKeyNotify + dhcReadControl
Global Const dhcKeyWrite = dhcKeySetValue + dhcKeyCreateSubKey + dhcReadControl
Global Const dhcKeyExecute = dhcKeyRead
Global Const dhcKeyAllAccess = dhcKeyQueryValue + dhcKeySetValue + _
dhcKeyCreateSubKey + dhcKeyEnumerateSubKeys + _
dhcKeyNotify + dhcKeyCreateLink + dhcReadControl
Global Const dhcHKeyClassesRoot = &H80000000
Global Const dhcHKeyCurrentUser = &H80000001
Global Const dhcHKeyLocalMachine = &H80000002
Global Const dhcHKeyUsers = &H80000003
Global Const dhcHKeyPerformanceData = &H80000004

Public Function dhReadRegistry(ByVal lngKeyToGet As Long, sKeyName As String, sKeyValue As String)
Dim hKeyDesktop As Long
Dim lngResult As Long
Dim strBuffer As String
Dim cb As Long

' Open the Requested Key (sKeyName) in the Registry
lngResult = RegOpenKeyEx(lngKeyToGet, sKeyName, 0&, dhcKeyAllAccess, hKeyDesktop)

' Make sure the call succeeded
If lngResult = dhcSuccess Then
' Create the buffer
strBuffer = Space(255)
cb = Len(strBuffer)

' Read the Key value stored in the Registry
lngResult = RegQueryValueEx(hKeyDesktop, sKeyValue, 0&, dhcRegSz, ByVal strBuffer, cb)

' Check return value
If lngResult = dhcSuccess Then
' Return the current value
dhReadRegistry = Left(strBuffer, cb - 1)
End If

' Close the Registry Key
lngResult = RegCloseKey(hKeyDesktop)
End If

End Function

Public Function dhWriteRegistry(ByVal lngKeyToGet As Long, sKeyName As String, sKeyValue As String, sNewValue As String) As Boolean
Dim hKeyDesktop As Long
Dim lngResult As Long

' Open the Requested Key (sKeyName) in the Registry
lngResult = RegOpenKeyEx(lngKeyToGet, sKeyName, 0&, dhcKeyAllAccess, hKeyDesktop)

' Make sure the call succeeded
If lngResult = dhcSuccess Then
' Save the value to the Registry
lngResult = RegSetValueEx(hKeyDesktop, sKeyValue, 0&, dhcRegSz, ByVal sNewValue, Len(sNewValue))

' Check return value
If lngResult = dhcSuccess Then
' Success
dhWriteRegistry = True
Else
' Failure
dhWriteRegistry = False
End If

' Close the Registry Key
lngResult = RegCloseKey(hKeyDesktop)
End If

End Function

 

[bblekfeld]

I know this is getting REALLY complicated, but is there a way you could code this to split the report into many different pdf files based on the each page, or each different record id?

 

[TonyVBACoder]

bblekfeld:

One way to print each "record id" in it's own PDF file would be to modify the "PrintReportToPDF" subroutine to include a recordset object and some looping code. For instance:
1) Create a Recordset object based on the data that you would be running the entire report on.
2) You would have to modify my "PrintReportToPDF" subroutine to loop through your recordset and within each loop, call the "DoCmd.OpenReport sReportName, acViewNormal" passing the "record id" as an additional argument to the Filter property for the report so that the report would only display data for the one "record id". Of course you would need to change the PDF file name in your registry each time through your recordset loop before you call the "DoCmd.OpenReport".
3) Remember, you should only modify your Registry settings for the default printer before you begin your loop and then reset it after you have read all the records in your recordset. In my example, I am only running one PDF report, so I set it to the PDF printer before I open the report and then I reset it to my default printer after I have opened the report.

 

[fmientjes]

We want to use Acrobat 6 or another PDF writer to make PDF files from VBA code in Access (like above).
(Acrobat 5 gives problems with faxing in Exchange 2003).

First question: which PDf writer to choose?
Second question: what changes are needed in the code to make it print straight to a PDF file?

It would save me a lot of time figuring it out.

Thanks,
Frans