Solved

Generate PDF file from Access 2000 and Access 2002 Report

Posted on 2004-04-29
23
342 Views
Last Modified: 2007-12-19
I am trying to generate a pdf file from an Access 2000 and/or Access 2002 report.  I would like to be able to create the pdf and save the it to a specific directory all in code.  Please help.
0
Comment
Question by:jenniferd732
  • 11
  • 6
  • 2
  • +2
23 Comments
 
LVL 1

Expert Comment

by:moralju
ID: 10955101
Edit every report to print to the AdobePDF print driver
then
DoCmd.OpenReport "Report Name1", acViewNormal
0
 

Author Comment

by:jenniferd732
ID: 10955143
I need to do it with VBA code because there are about 100 reports.  I modify a query and set the report's recordsource.  Then I run the report using docmd.  Now I need to print to the PDF Distiller (Acrobat 6.0) and save the pdf file all in code.  The problem I am having is how do I print the pdf and save it programatically?
0
 
LVL 34

Expert Comment

by:flavo
ID: 10955238
0
 

Author Comment

by:jenniferd732
ID: 10955315
Thanks Dave, but I would like to not have to purchase anything if possible.  I have also tried the other example that link provided, but with no luck.  I have seen what looks like some registry manipulation to store what the PDF file name is to be called, but have no idea on how to do this.  I would really like to create the pdf's and save them all in a directory and setting the name of the pdf during the save process in code, but am having trouble doing that.
0
 
LVL 34

Accepted Solution

by:
flavo earned 250 total points
ID: 10955473
Trying to get it working with CutePDF (free) but i need to use sendkeys, and it ant working

code soo far - need to get a pause or something to make sure the file name is put into the pdf file name bit (hate using sendkeys!)  

Need to refrence MS Excel Obj Lib, used excel wait command, didnt work!

I need to do some work, so maybe you / someone else can do better with it..

Sub test()

Dim sFileandPath As String
Dim xlApp As Excel.Application
Dim newHour As Integer
Dim newMinute As Integer
Dim newSecond As Integer
Dim waitTime As Date

sFileandPath = "c:\test.pdf"

Set xlApp = New Excel.Application

DoCmd.OpenReport "rptTest", acViewPreview
DoCmd.PrintOut acPrintAll, 1, 1, acHigh


DoEvents

MsgBox "ready to print?"


newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)

xlApp.Wait waitTime
DoEvents

SendKeys sFileandPath, False
SendKeys "{Enter}", False


End Sub
0
 

Author Comment

by:jenniferd732
ID: 10955493
I found this which apparently works, but I don't know how to do the registry part.  Maybe if we can figure that out it might work???
0
 
LVL 34

Expert Comment

by:flavo
ID: 10955515
nearly there


Dim sFileandPath As String
Dim xlApp As Excel.Application
Dim newHour As Integer
Dim newMinute As Integer
Dim newSecond As Integer
Dim waitTime As Date

sFileandPath = "c:\test2.pdf"

Set xlApp = New Excel.Application

DoCmd.OpenReport "rptTest", acViewPreview
DoCmd.PrintOut acPrintAll, 1, 1, acHigh


DoEvents




newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)

xlApp.Wait waitTime
DoEvents
AppActivate "Save As", True

SendKeys sFileandPath, True
SendKeys "{Enter}", True
0
 
LVL 34

Expert Comment

by:flavo
ID: 10955517
which registery bit???
0
 

Author Comment

by:jenniferd732
ID: 10955529
Opps...forgot the link, but maybe we won't need it if you are close...
0
 

Author Comment

by:jenniferd732
ID: 10955532
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jenniferd732
ID: 10955648
If I change the default printer, then run this line of code:

DoCmd.OpenReport "Test"

A prompt comes up that asks me to save the pdf.  This is the part I want to automate from code.  Please Help....
0
 
LVL 34

Expert Comment

by:flavo
ID: 10955663
Do you want the file name done automoatically or not??
0
 

Author Comment

by:jenniferd732
ID: 10955670
Yes, that would be wonderful!
0
 

Author Comment

by:jenniferd732
ID: 10955734
Apparently setting value for PDFFileName in the registry stops file dialog box from appearing.  How do I set this registry value?
0
 
LVL 34

Expert Comment

by:flavo
ID: 10955749
ill see... a little busy trying to get a job out the door
0
 

Expert Comment

by:dtomlin
ID: 10955807
I do this all the time, but you cant do it in ACCESS alone.  You need some type of PDF converter.   Personally I like to use the AMYUNI print driver.  What it does is acts like a printer on the machine.  Once installed, you can print from any windoze application.    I like to make reports that run via a macro at night (1,000's of page sometimes).  Just set you printer to the PDF converter and VIOLA  you got PDF's on the hard driver (Or network)   If you code you can even design webpages with links to each of the PDF files.  The options are limitless with this converter - if you can print you can make PDF's - PERIOD!  Visit www.amyuni.com  The cost I thing is $85 per license - instal takes about 30 seconds and your "printing" pdfs to disk 30 seconds later.  Cheers!  Dan
0
 

Author Comment

by:jenniferd732
ID: 10956066
I have the solution:

Code as follows:

Public Sub CreatePDF()

    Dim db As DAO.Database
    Set db = CurrentDb
   
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("Test")
   
    'TODO: change to PDFWriter printer here
   
    Do While Not rs.EOF
        Call SaveFormAsPDF("Test", "C:\Temp\" & rs!ID & ".pdf", "ID=" & rs!ID & "")
        rs.MoveNext
    Loop
   
    rs.Close
    Set db = Nothing
    'TODO: change back to default printer here
   
End Sub

Public Sub SaveFormAsPDF(strReportName As String, strPath As String, strFilter As String)

    Dim RetVal As Variant
   
    'set registry values
    SetKeyValue "Software\Adobe\Acrobat PDFWriter", "PDFFilename", strPath, REG_SZ
    SetKeyValue "Software\Adobe\Acrobat PDFWriter", "bExecViewer", 0, REG_SZ
   
    'open report with filter
    DoCmd.OpenReport strReportName, acViewPreview, , strFilter
    DoCmd.PrintOut
    DoCmd.Close acReport, strReportName

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

Public Function QueryValueEx(ByVal lhKey As Long, ByVal szValueName As _
    String, vValue As Variant) As Long
   
On Error GoTo QueryValueExError

    Dim cch As Long
    Dim lrc As Long
    Dim lType As Long
    Dim lValue As Long
    Dim sValue As String
   
    ' 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


I found the basis for this code at:
http://www.tek-tips.com/gfaqs.cfm/lev2/4/lev3/27/pid/705/fid/1635
AND
http://www.tek-tips.com/gviewthread.cfm/pid/705/qid/816439
0
 

Author Comment

by:jenniferd732
ID: 10956072
Thanks to everyone for their input and effort, but I solved the problem on my own.

Thanks,
Jennifer
0
 

Expert Comment

by:dtomlin
ID: 10956135
So what dd you do?  Expert's Exchange please!
0
 

Author Comment

by:jenniferd732
ID: 11001236
The solution is in the comment that I added on 04/29/2004 11:10PM CDT.  
0
 
LVL 1

Expert Comment

by:seanmrmd
ID: 11654400
Will this work with Acrobat 6.0?
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

706 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

19 Experts available now in Live!

Get 1:1 Help Now