How to print MS Access 2000 report to PDF995 printer by VBA Code

[dexterb]

I cannot install any pdf writers on the machine. I need to print a MS access 2000 report to a pdf file so I can email this. I already have a PDF995 printer in my windows list of printers. All this needs to be done by VBA code.

[pks00]

Sweet, your using pdf995, makes answering this question that much easier :)


okay, here is some vba code. Ive got a function called pdfwrite which you call passing in the name of the report, pdf destination and any criteria for running the report. Add this lot to a module

Look for comments saying "IMPORTANT
u need to change the paths to reflect your installation

Declare Function GetPrivateProfileString Lib "kernel32" Alias _
   "GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
   ByVal lpKeyName As Any, ByVal lpDefault As String, _
   ByVal lpReturnedString As String, ByVal nSize As Long, _
   ByVal lpFileName As String) As Long
 
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
 
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
 
Sub pdfwrite(reportname As String, destpath As String, Optional strcriteria As String)
 
    Dim syncfile As String, maxwaittime As Long
    Dim iniFileName As String, tmpPrinter As Printer
    Dim outputfile As String, X As Long
    Dim tmpoutputfile As String, tmpAutoLaunch As String
 
'***** IMPORTANT - SEE THIS *****
    ' set the location of the PDF995.ini and the pdfsync files
    iniFileName = "C:\Program Files\pdf995\res\pdf995.ini"
    syncfile = "c:\documents and settings\all users\application data\pdf995\pdfsync.ini"
 
    If Mid(destpath, Len(destpath), 1) <> "\" Then destpath = destpath & "\"
    outputfile = destpath & reportname & ".pdf"
 
    tmpoutputfile = ReadINIfile("PARAMETERS", "Output File", iniFileName)
    tmpAutoLaunch = ReadINIfile("PARAMETERS", "Autolaunch", iniFileName)
 
    On Error Resume Next
    Kill outputfile
    On Error GoTo Cleanup
 
    X = WritePrivateProfileString("PARAMETERS", "Output File", outputfile, iniFileName)
    X = WritePrivateProfileString("PARAMETERS", "AutoLaunch", "0", iniFileName)
 
    Set tmpPrinter = Application.Printer
    Application.Printer = Application.Printers("PDF995")
 
    DoCmd.OpenReport reportname, acViewNormal, , strcriteria
 
    Sleep (10000)
    maxwaittime = 300000 'If pdf995 isn't done in 5 min, quit anyway
    Do While ReadINIfile("PARAMETERS", "Generating PDF CS", syncfile) = "1" And maxwaittime > 0
        Sleep (10000)
        maxwaittime = maxwaittime - 10000
    Loop
 
Cleanup:
    Sleep (10000)
    X = WritePrivateProfileString("PARAMETERS", "Output File", tmpoutputfile, iniFileName)
    X = WritePrivateProfileString("PARAMETERS", "AutoLaunch", tmpAutoLaunch, iniFileName)
    X = WritePrivateProfileString("PARAMETERS", "Launch", "", iniFileName)
    On Error Resume Next
 
    Application.Printer = tmpPrinter
 
End Sub
 
Function ReadINIfile(sSection As String, sEntry As String, sFileName As String) As String
 
    Dim X As Long
    Dim sDefault As String
    Dim sRetBuf As String, iLenBuf As Integer
    Dim sValue As String
 
    sDefault$ = ""
    sRetBuf$ = String$(256, 0)   '256 null characters
    iLenBuf% = Len(sRetBuf$)
    X = GetPrivateProfileString(sSection, sEntry, _
               sDefault$, sRetBuf$, iLenBuf%, sFileName)
    ReadINIfile = Left$(sRetBuf$, X)
 
End Function
 

[dexterb]

Hi,
Thanks for the speedy reply.

I tried the code but did not work. I did not get any errors though.
Firstly, I cannot find the file
syncfile = "c:\documen ts and settings\all users\applicati on data\pdf995\pdf sync.ini"
I searched for it in my entire C: drive. I do not have any other drives.

Secondly, I had to comment the following code as it is not supported by MS Access 2000;
'tmpPrinter As Printer
'Set tmpPrinter = Application.Pri nter
'Application.Pr inter = Application.Pri nters("PDF995")
'Application.Pr inter = tmpPrinter

[dexterb]

Hi,

This code that you gave me was not working. I tried it again today, and for some reason, it works superbly without any errors. I am not sure why, maybe I needed to reboot my machine or something...Any ways, thanks...

[pks00]

Glad its working.
Ive tested this code and it works sweet for me. It now appears its working for you. Not sure why it didnt work in the first place, I can only imagine it wasnt working due to some configuration issue.

[manoj9849967222]

Sweet, your using pdf995, makes answering this question that much easier :)


okay, here is some vba code. Ive got a function called pdfwrite which you call passing in the name of the report, pdf destination and any criteria for running the report. Add this lot to a module

Look for comments saying "IMPORTANT
u need to change the paths to reflect your installation

Declare Function GetPrivateProfileString Lib "kernel32" Alias _
   "GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
   ByVal lpKeyName As Any, ByVal lpDefault As String, _
   ByVal lpReturnedString As String, ByVal nSize As Long, _
   ByVal lpFileName As String) As Long
 
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
 
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
 
Sub pdfwrite(reportname As String, destpath As String, Optional strcriteria As String)
 
    Dim syncfile As String, maxwaittime As Long
    Dim iniFileName As String, tmpPrinter As Printer
    Dim outputfile As String, X As Long
    Dim tmpoutputfile As String, tmpAutoLaunch As String
 
'***** IMPORTANT - SEE THIS *****
    ' set the location of the PDF995.ini and the pdfsync files
    iniFileName = "C:\Program Files\pdf995\res\pdf995.ini"
    syncfile = "c:\documents and settings\all users\application data\pdf995\pdfsync.ini"
 
    If Mid(destpath, Len(destpath), 1) <> "\" Then destpath = destpath & "\"
    outputfile = destpath & reportname & ".pdf"
 
    tmpoutputfile = ReadINIfile("PARAMETERS", "Output File", iniFileName)
    tmpAutoLaunch = ReadINIfile("PARAMETERS", "Autolaunch", iniFileName)
 
    On Error Resume Next
    Kill outputfile
    On Error GoTo Cleanup
 
    X = WritePrivateProfileString("PARAMETERS", "Output File", outputfile, iniFileName)
    X = WritePrivateProfileString("PARAMETERS", "AutoLaunch", "0", iniFileName)
 
    Set tmpPrinter = Application.Printer
    Application.Printer = Application.Printers("PDF995")
 
    DoCmd.OpenReport reportname, acViewNormal, , strcriteria
 
    Sleep (10000)
    maxwaittime = 300000 'If pdf995 isn't done in 5 min, quit anyway
    Do While ReadINIfile("PARAMETERS", "Generating PDF CS", syncfile) = "1" And maxwaittime > 0
        Sleep (10000)
        maxwaittime = maxwaittime - 10000
    Loop
 
Cleanup:
    Sleep (10000)
    X = WritePrivateProfileString("PARAMETERS", "Output File", tmpoutputfile, iniFileName)
    X = WritePrivateProfileString("PARAMETERS", "AutoLaunch", tmpAutoLaunch, iniFileName)
    X = WritePrivateProfileString("PARAMETERS", "Launch", "", iniFileName)
    On Error Resume Next
 
    Application.Printer = tmpPrinter
 
End Sub
 
Function ReadINIfile(sSection As String, sEntry As String, sFileName As String) As String
 
    Dim X As Long
    Dim sDefault As String
    Dim sRetBuf As String, iLenBuf As Integer
    Dim sValue As String
 
    sDefault$ = ""
    sRetBuf$ = String$(256, 0)   '256 null characters
    iLenBuf% = Len(sRetBuf$)
    X = GetPrivateProfileString(sSection, sEntry, _
               sDefault$, sRetBuf$, iLenBuf%, sFileName)
    ReadINIfile = Left$(sRetBuf$, X)
 
End Function
 

HI pks00

I was going through the codes as i have a similar problem. I want to generate the report in PDF format when a command button is clicked

But as i am new to the access programming. I could not understand it properly.

Could you please help me out as to How to generate a report in PDF format when a command button is clicked. I too Have "PDF995" as well as "Cute PDF writer"

Please help me out.
Thank you

Regards
Manoj