RunReportAsPDF

[Warren]

Hi all,

I have come across some brilliant code that prints Access Reports as
PDFs. Using the registry, it changes your default printer and then
resets the original when done.

My understanding is that it is a little old and thus I have a problem
where the old registry keys are different to the new (i think). My
problem is the 'Save As' dialogue that opens when using this function;
I don't want it! - The code allows for a pre-defined filename but I
think that this doesn't work with Acrobat 7...

Anyway, any help with THIS would be great as third-party software
downloads are not an option for me on this restricted system.

The complete code is below.

Thanks in advance!




'**************************************
'Windows API/Global Declarations for :Cr
' eate PDF from MS Access Report
'**************************************


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


Declare Function RegOpenKeyEx Lib "advapi32" 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 RegSetValueEx Lib "advapi32" Alias "RegSetValueExA"
(ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As
Long, ByVal dwType As Long, ByVal szData As String, ByVal cbData As
Long) As Long


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


Declare Function RegCreateKeyEx Lib "advapi32" 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, lpSecurityAttributes As SECURITY_ATTRIBUTES, phkResult As Long,
lpdwDisposition As Long) As Long


#If Win32 Then
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 KEY_ALL_ACCESS = &H3F
Public Const REG_OPTION_NON_VOLATILE = 0&
Public Const REG_CREATED_NEW_KEY = &H1
Public Const REG_OPENED_EXISTING_KEY = &H2
Public Const ERROR_SUCCESS = 0&
Public Const REG_SZ = (1)
#End If


Type SECURITY_ATTRIBUTES
nLength As Long
lpSecurityDescriptor As Long
bInheritHandle As Boolean
End Type
'**************************************
' 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").
'
' Returns:None
'
'Assumes:This code is easily modified to
' be used in other programs
'
'Side Effectslease use the most recent
' installs of Adobe Exchange or PDFWriter
' to ensure proper functionality.
'This code is copyrighted and has limite
' d warranties.
'Please see http://www.Planet-Source-Cod
' e.com/xq/ASP/txtCodeId.35321/lngWId.1/qx
' /vb/scripts/ShowCode.htm
'for details.
'**************************************



Public Function bGetRegValue(ByVal hKey As Long, ByVal sKey As String,
ByVal sSubKey As String) As String
Dim lResult As Long
Dim phkResult As Long
Dim dWReserved As Long
Dim szBuffer As String
Dim lBuffSize As Long
Dim szBuffer2 As String
Dim lBuffSize2 As Long
Dim lIndex As Long
Dim lType As Long
Dim sCompKey As String
Dim bFound As Boolean
lIndex = 0
lResult = RegOpenKeyEx(hKey, sKey, 0, 1, phkResult)


Do While lResult = ERROR_SUCCESS And Not (bFound)
szBuffer = Space(255)
lBuffSize = Len(szBuffer)
szBuffer2 = Space(255)
lBuffSize2 = Len(szBuffer2)
lResult = RegEnumValue(phkResult, lIndex, szBuffer, lBuffSize,
dWReserved, lType, szBuffer2, lBuffSize2)


If (lResult = ERROR_SUCCESS) Then
sCompKey = Left(szBuffer, lBuffSize)


If (sCompKey = sSubKey) Then
bGetRegValue = Left(szBuffer2, lBuffSize2 - 1)
RegCloseKey phkResult
Exit Function
End If
End If
lIndex = lIndex + 1
Loop
RegCloseKey phkResult
End Function


Public Function bSetRegValue(ByVal hKey As Long, ByVal lpszSubKey As
String, ByVal sSetValue As String, ByVal sValue As String) As Boolean
On Error Resume Next
Dim phkResult As Long
Dim lResult As Long
Dim SA As SECURITY_ATTRIBUTES
Dim lCreate As Long
RegCreateKeyEx hKey, lpszSubKey, 0, "", REG_OPTION_NON_VOLATILE,
KEY_ALL_ACCESS, SA, phkResult, lCreate
lResult = RegSetValueEx(phkResult, sSetValue, 0, REG_SZ, sValue,
CLng(Len(sValue) + 1))
RegCloseKey phkResult
bSetRegValue = (lResult = ERROR_SUCCESS)
End Function


Public Function RunReportAsPDF(rptName As String, sPDFPath As String,
sPDFName As String)
'---------------------------------
'rptName = Microsoft Access report name
' you
'want to create pdf from
'sPDFPath = the directory path where you
' want
'to create the pdf file (ex. - "c:\data\
' ")
'sPDFName = the name of the pdf file you
' are
'wanting to create (ex. - "file001.pdf")
'
'---------------------------------
Dim sMyDefPrinter As String
On Error GoTo Err_RunReport
'Save current default printer
sMyDefPrinter = bGetRegValue(HKEY_CURRENT_USER,
"Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device")
' Set default printer to PDF Writer
bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\Windows
NT\CurrentVersion\Windows", "Device", "Acrobat PDFWriter"
'Setting value for PDFFileName in the re
' gistry stops file dialog box from appear
' ing
bSetRegValue HKEY_CURRENT_USER, "Software\Adobe\Acrobat PDFWriter",
"PDFFileName", sPDFPath + sPDFName
'Run the report


DoCmd.OpenReport rptName, acViewNormal
Exit_RunReport:
' Restore default printer
bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\WIndows
NT\CurrentVersion\Windows", "Device", sMyDefPrinter
Exit Function
Err_RunReport:
MsgBox Err.Description
Resume Exit_RunReport
End Function

 

[Albert D. Kallal]

On the other hand, that adobe PDF create is rather expensive, and is RATHER
huge in size..

any help with THIS would be great as third-party software
downloads are not an option for me on this restricted system.

Err....hum...the how did you get that huge adobe program installed?
That don't count as huge install?? When you update adobe, or download
a update, your software then might stop working...

There is a great pdf add-in for ms-access here:
http://www.lebans.com/reporttopdf.htm

The above is really nice, since you don't have to have a PDF printer driver
installed, nor even install any pdf software
on your computer.

You do have to place a dll in
the same dir as the mdb/mde file or system32 directory. However, placing one
small dll in your mdb directory is
GRAND CANYON easier then having to have adode installed on the target
machine. And, further, that
code example also eliminates the need to try and switch the printer. The
example also allows you to
save a pdf file to disk via code with a name you choose...

Give the above a try...as it will work on machines that don't even have
adode installed...

 

[Warren]

Thank you Albert but:

- The system I am developing in is heavily restricted. I cannot
download .zip files and I certainly cannot write to the Windows System
folder.

- Every one of these 3000 machines shares the same software and
configuration.

Thus I still need help!!!

 

[Albert D. Kallal]

Well, in a2002 and later, there is a printer object, so, to change the
printer, you don't need special code....
You don't mention what version\ of ms-access.


You can use:

Set Application.Printer = Application.Printers("HP LaserJet Series II")

So, to save/switch, you can use:

dim strDefaultPrinter as string

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

' switch to printer of your choice:

Set Application.Printer = Application.Printers("HP LaserJet Series II")

do whatever.

Swtich back.

Set Application.Printer = Application.Printers(strDefaultPrinter)

So, that should eliminate a mess of code to switch printers....

 

[Warren]

That's good to know Albert.

I am using A2000.

My problem does not relate to printers. All I need to do is set the
file name of the PDF to be created so that I don't get the SAVE AS
dialog.

 

[Albert D. Kallal]

That's good to know Albert.

I am using A2000.

My problem does not relate to printers. All I need to do is set the
file name of the PDF to be created so that I don't get the SAVE AS
dialog.

Actually, it does!!

If your default printer is not the pdf printer, then how will you launch, or
even have the pdf dialogs come up?

Even in the case were you want to set the output file name in code, you
STILL HAVE to set your printer to the pdf, then print....else when you
generate the report..it will go to the current default printer! So, this
code example very much does relate to printers, since the creating of PDF
files is done (in most cases) by printing to a pdf printer.

So, you STILL have to have some code that changes the default printer here.
Or are you suggesting that you will have to ask users to change their
default printer to the pdf printer BEFORE they run this code? This is likely
much more hassle then solving the problem of users not having to enter the
pdf file name.

I don't know, and cant see what your code example is now, but I have to
assume and expect that this code does in fact try and set the default
printer..and THEN prints the report to the pdf printer. It really can't work
much any other way. You could check with the author of the code you are
trying to use (it is impossible for me to even being to guess what is wrong
with your code here).

So, you are going to need several things

- ensure that a pdf printer (assuming adobe distiller in this case) is
installed
- ensure that if you update the pdf version of adobe, that your code
will STILL work
with this version of adobe
(often, updating the adobe to the next version can break your code)
- either have users change to the adobe pdf printer, or have your code
do this

As mentioned, the link I gave you does not requite any software to be
"installed" on the computer. further, that software example does NOT require
you to change the printer like your current pdf example does. And, further,
the example I gave you does not require that the pdf printer be installed.

So, you are asking for help, but not really giving any options for anyone to
help you. You are not allowed to install any software, and so that really
supports the suggested solution (as that solution only requires that you
copy some files to your computer (no installing need be done). After all,
they seem to allow YOU to write YOUR software, and then copy that to each
computer now...so, why not just copy a few extra files in the process, as
that is what they allow now....

 

[Warren]

Albert,

The Code
-----------------------------------------------------------------
The code changes the default printer to the PDF printer (via the
registy).

The code attempts to specify a file name (this is where my problem is)
but is unsuccessful thus it asks for a file name via the dialogue box.

Then it prints to PDF.

Then re sets the default printer back to the orginal.

The code works, and works well; I just do not want the dialogue box.


My Situation
-----------------------------------------------------------------
I do not have a computer, I have a terminal (dumb, I know) but I have
to work with what I have.

I cannot write to any drive other my home drive, or my group's share
drive.

I cannot download or receive anything other than documents.

I am running Acrobat 7 and I believe the code was made for Acrobat 4.


My Problem
--------------------------------------------------------------------
I believe lies in this line ONLY:
bSetRegValue HKEY_CURRENT_USER, "Software\Adobe\Acrobat PDFWriter",
"PDFFileName", sPDFPath + sPDFName


I will try to get your solution onto my home drive by external means.
If it works, I will let you know, otherwise, let's just leave it, ok?

 

[Warren]

My Problem

Per my previous post, I was right!

Replace the above with the below and it works EXACTLY how I want it to!
(It only took me a month to work it out...)