Solved

Convert Access Report to PDF file

Posted on 2003-11-28
9
2,063 Views
Last Modified: 2013-11-28
Hi,

I would like to programmatically convert Access report to PDF file using PDF995. Anyone has the idea how to do it?

Thanks in advance.
0
Comment
Question by:ourselves
  • 4
  • 4
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 9836903
You install pdf995 as a printer.
Then select it as your printer in Access and run your report.

Pete

0
 

Author Comment

by:ourselves
ID: 9836922
Thanks
But I'd like to convert the report in VB code.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 9837030
Well even in code you would still have to programmatically select pdf995 as your printer and then open your report.

Do you mean VB or VBA?
If VBA which version of Access?

Pete

0
 
LVL 2

Accepted Solution

by:
rthomsen earned 50 total points
ID: 9839480
I have some code that programatically changes the default printer to Acrobat PDF Writer and back again.  It is used in the following manner when exporting a report:

call ChangeToAcrobat
Call ChangePdfFileName(FileName)
DoCmd.OpenReport "ReportName"              'Print the report to acrobat
DoCmd.Close acReport, "ReportName"
ResetDefaultPrinter                                   'reset the default printer

Here are the Acrobat Functions.  I put them all in a seperate global code module:

Option Compare Database
Option Explicit

'This code is based on code from:
' Microsoft Access 95 How-To
' (c) 1998 Ken Getz and Paul Litwin
' All rights reserved.

Declare Function aht_apiGetProfileString Lib "kernel32" Alias "GetProfileStringA" (ByVal strAppName As String, ByVal strKeyName As String, ByVal strDefault As String, ByVal strReturned As String, ByVal lngSize As Long) As Long
Declare Function aht_apiWriteProfileString Lib "kernel32" Alias "WriteProfileStringA" (ByVal strAppName As String, ByVal strKeyName As String, ByVal strValue As String) As Integer
Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long

Type aht_tagDeviceRec
   drDeviceName As String
   drDriverName As String
   drPort As String
End Type
Public drexisting As aht_tagDeviceRec

Public Function ChangeToAcrobat()
'This function gets the current default printer, saves it in the variable drexisting and then sets the default printer
'to be the acrobat writer to generate the pdf file
Dim dr As aht_tagDeviceRec

If ahtGetDefaultPrinter(drexisting) Then                'Get the current default printer and save it
    dr.drDeviceName = "Acrobat PDFWriter"                   'set the default printer to be acrobat writer
    dr.drDriverName = "PDFWRITR"
    dr.drPort = "LPT1:"
    Call ahtSetDefaultPrinter(dr)                      'reset the default printer
End If
End Function

Function ahtGetDefaultPrinter(dr As aht_tagDeviceRec) As Boolean
Dim strBuffer As String

strBuffer = ahtGetINIString("Windows", "Device")

If Len(strBuffer) > 0 Then
    dr.drDeviceName = ahtGetToken(strBuffer, ",", 1)
    dr.drDriverName = ahtGetToken(strBuffer, ",", 2)
    dr.drPort = ahtGetToken(strBuffer, ",", 3)
    ahtGetDefaultPrinter = True
Else
    ahtGetDefaultPrinter = False
End If
End Function

Private Function ahtGetINIString(ByVal strGroup As String, ByVal strItem As String) As Variant
   ' Get a string value from the WIN.INI file. For example, the following, in the Immediate Window:
   ' ? ahtGetINIString("Windows", "Device") might display: HP LaserJet 4,HPPCL5E,LPT1:
Dim intChars As Integer
Dim strBuffer As String

strBuffer = String(255, 0)
intChars = aht_apiGetProfileString(strGroup, strItem, "", strBuffer, 255)
ahtGetINIString = left(strBuffer, intChars)
End Function

Function ahtGetToken(ByVal strValue As String, ByVal strDelimiter As String, ByVal intPiece As Integer) As Variant
Dim intPos As Integer
Dim intLastPos As Integer
Dim intNewPos As Integer

On Error GoTo ahtGetTokenExit

' Make sure the delimiter is just one character.
strDelimiter = left(strDelimiter, 1)

' If the delimiter doesn't occur at all, or if
' the user's asked for a negative item, just return the item
' they passed in.

If (InStr(strValue, strDelimiter) = 0) Or (intPiece <= 0) Then
    ahtGetToken = strValue
Else
    intPos = 0
    intLastPos = 0
    Do While intPiece > 0
        intLastPos = intPos
        intNewPos = InStr(intPos + 1, strValue, strDelimiter)
        If intNewPos > 0 Then
            intPos = intNewPos
            intPiece = intPiece - 1
        Else
            ' Catch the last piece, where there's no trailing token.
            intPos = Len(strValue) + 1
            Exit Do
        End If
    Loop
    If intPiece > 1 Then
        ahtGetToken = Null
    Else
        ahtGetToken = Mid$(strValue, intLastPos + 1, intPos - intLastPos - 1)
    End If
End If

ahtGetTokenExit:
   Exit Function

ahtGetTokenErr:
   MsgBox "Error in ahtGetToken: " & Error & " (" & Err & ")"
   Resume ahtGetTokenExit
End Function

Private Function ahtSetDefaultPrinter(dr As aht_tagDeviceRec) As Boolean
Dim strBuffer As String

' Build up the appropriate string.
strBuffer = dr.drDeviceName & ","
strBuffer = strBuffer & dr.drDriverName & ","
strBuffer = strBuffer & dr.drPort

' Now write that string out to WIN.INI.
ahtSetDefaultPrinter = (aht_apiWriteProfileString("Windows", "Device", strBuffer) <> 0)
End Function

Public Sub ChangePdfFileName(NewFileName As String)
Call WritePrivateProfileString("Acrobat PDFWriter", "PDFFileName", NewFileName, "c:\winnt\system32\spool\drivers\w32x86\2\__pdf.ini")
'Call aht_apiWriteProfileString("Acrobat PDFWriter", "PDFFileName", NewFileName)
End Sub

Public Sub ResetDefaultPrinter()
   Call ahtSetDefaultPrinter(drexisting)    'Reset the default printer
End Sub
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 77

Expert Comment

by:peter57r
ID: 9841795
'This code is based on code from:
' Microsoft Access 95 How-To
' (c) 1998 Ken Getz and Paul Litwin
' All rights reserved.

Then you shouldn't be publishing it.  This is not public domain code. You acquire rights to using it by buying the book.

Pete
0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9850607
That's untrue.  I received the code from a webpage with a disclaimer stating that the code could be used in any application as long as the copyright.
0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9850625
Here is the associated web page.

http://www.mvps.org/access/reports/rpt0011.htm
0
 
LVL 77

Expert Comment

by:peter57r
ID: 9850823
I apologise totally.

When code is placed in to the Access Web it usually carries embedded comments on it's permitted use.

Pete
0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9850910
No Problem.  The legality surrounding software use is a sticky subject.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now