Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Report Caption in pdf (XP/03)

    i have the following code in a report to modify the caption on the fly:
    Dim strCaption as string
    strCaption ="Incidents to Date for "
    Me.Caption = strCaption & " " & [Forms]![frmReportSelection]![cboReg] & " Only "

    It works marvelously when previewing the report. However, when we print it to an Acrobat file it prompts with the original caption in the report, and not the one above.

    Does anyone have any experience with creating pdf files from Access where Access sends the file name automatically?

    thanks,

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 30 Times in 30 Posts

    Re: Report Caption in pdf (XP/03)

    See if the code from <post:=322,094>post 322,094</post:> helps. Another option might be to use code to rename the PDF file after it has been created.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Report Caption in pdf (XP/03)

    Hans,

    Thanks again as usual. I read the entire thread, and see what a great job you do and see that not everyone recognizes the work you, Francois and others do in supporting the board and supporting us neophytes.

    After going through the mentioned thread, I tried "ready4data" solution which ALMOST did what I needed. I contacted him directly and he provided a very nice, elegant solution that did exactly what I was seeking AND I think I even understand how it works.

    Thank you again for you assistance, and your time.

    Ken

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,607
    Thanks
    0
    Thanked 32 Times in 32 Posts

    Re: Report Caption in pdf (XP/03)

    You should post the solution in here so people can learn by it.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    First let me apologize for having not done this sooner. I got a message today from grovelli wanting me to post the solution, and when I looked at this thread I realized that I had not responded to Patt's post.

    In order to accomplish this you must get Stephen Leban's "ConvertReportToPDF". His web site is http://www.lebans.com.

    I have stripped down the code substantially, so there may be some fluff I missed. As I am not a professional programmer, this was a struggle for me, and is not the quality of work like Hans, Patt, MarkLiquorman and others have posted. But it did work for me. I hope the code is not too convoluted.

    In a nutshell, I have the user select a pdf printer from a combo box on the form. Using "Public Function ChangeDefault2PDF" I then check to see if they have that pdf printer a printer on their machine, and if they do, I save their existing default printer to a variable, then change the default printer to selected pdf. This will be changed at the end of the process where the default printer is set back to their original default.

    I then set and create as necessary the paths to where I want the pdf's to reside. Lastly, I have a table that contains the names of the reports I want to run, what I want the final pdf file to be named, the folder name where I want the pdf file created, and a couple of other fields that are not required to accomplish the pdf creation. I create a recordset with this data, and loop through the reports one at a time, and create the appropriate pdf file.



    [codebox]
    Private Sub cmd_DoEmAll_Click()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strPath As String
    Dim strName As String
    Dim strFullPath
    Dim strFileName As String
    Dim strPDFRootPath As String
    Dim strPathName As String

    Dim tmpPrinter As Printer
    Dim FileName As String
    Dim fso As Object

    On Error GoTo Err_cmd_DoEmAll_Click

    Dim MnthYear As String
    MnthYear = Format$([Forms]![frm_IReports]![txtReportEndDate], "MMMYYYY")

    ' create directories
    Set fso = CreateObject("Scripting.FileSystemObject")
    strPath = "C:\AIR\Spreadsheets\"
    strFullPath = strPath & MnthYear

    Set db = CurrentDb

    ' Create Monthly Folder
    ' ================================================== ==============================
    ===
    If fso.FolderExists(strFullPath) = False Then
    fso.CreateFolder (strFullPath) ' Create the directory
    End If

    ' Create Individual Folders inside the Monthly Folder
    ' ================================================== ==============================
    ===
    ' Read Location Names from lookup table that contains two fields, 'pkey' and 'Folder' where 'Folder' = Location Name
    strSQL = "select folder " & _
    "From tlkpMonthlyFolders"
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

    Do While rst.EOF = False
    If fso.FolderExists(strFullPath & "\" & rst!folder) = False Then
    fso.CreateFolder (strFullPath & "\" & rst!folder) ' Create the directory with Name of the Location
    End If
    rst.MoveNext
    Loop

    ' Set 'Success' to false in the Reports table (tlkpExportFields, a legacy table from used elsewhere)
    DoCmd.RunSQL "UPDATE tlkpExportFields SET Success = False;"


    '================================================= =======
    ' Set up variables for pdf printing
    '================================================= =======

    Dim blRet As Boolean
    Dim strPdfName As String
    Dim strPDFPath As String
    Dim stDocName As String
    Dim strPrinter As String

    strPDFRootPath = "C:\AIR\Spreadsheets\" & MnthYear & "\"
    strPDFPath = strFullPath & "\TopLevelGroup\" 'strPathName
    strPdfName = strPDFPath

    '================================================= =======
    ' Set default Printer to PDF printer



    ' Save the existing default printer for switching back and set up Adobe PDF as new printer
    ' and Set Printer defaults to PDF using the ChangeDefault2PDF function

    If ChangeDefault2PDF(Me.cboPrinter) Then
    Else ' If the PDF Printer is not available, prompt user
    MsgBox "You must select another printer"
    Me.cboPrinter.SetFocus
    Exit Sub
    End If


    ' Create recordset of Reports to print, with tlkpExportFields where:
    ' ExcelName = Name of the Access Report
    ' Worksheet = Name of pdf file
    ' ExportGroup = FolderName (mat

    strSQL = "SELECT QueryName, ExcelName, Worksheet, ExportGroup, Success " & _
    "FROM tlkpExportFields " & _
    "WHERE ExportGroup <> 'TopLvl' AND Run = true " & _
    "ORDER BY pkey;"


    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    If rst.EOF = False Then
    With rst
    .MoveFirst
    Do While Not .EOF
    strFileName = !Worksheet & " " & Format(Forms![frm_IReports]![txtReportEndDate], "MM_DD_YY") & ".pdf"
    ' strFileName becomes the name of the PDF file when it is created/saved and is used to test for existence

    strPDFPath = strPDFRootPath & !ExportGroup & "\"

    strName = strPDFPath & strFileName

    ' Test to see if file exists and delete it if it does
    If Not Dir(strName) = "" Then
    Kill (strName)
    End If

    stDocName = !ExcelName
    strPdfName = strPDFPath & strFileName

    ' Following line calls on Stephen Lebans' module and requires DynaPDF.dll, StrStorage.dll and clsCommonDialog
    ' see http://www.lebans.com

    blRet = ConvertReportToPDF(stDocName, , strPdfName, False, False)

    ' Update the tlkpExportFields record to indicate pdf created successfully
    .Edit
    !Success = True
    .Update
    .MoveNext
    Loop
    End With
    End If
    rst.Close
    Set rst = Nothing


    'switch back to original default printer
    Set tmpPrinter = Application.Printer
    Set Application.Printer = Application.Printers(0)

    End Sub



    Public Function ChangeDefault2PDF(txtPrinter As String) As Boolean
    On Error GoTo Err_ChangeDefault2PDF
    ChangeDefault2PDF = True
    Dim tmpPrinter As Printer
    ' ==================================================
    ' Set default printer to pdf driver
    ' ==================================================

    'Save the existing default printer for switching back and set up Adobe PDF as new printer
    Set tmpPrinter = Application.Printer 'Default Printer


    ' I have a combo box with pdf printers listed
    ' for the user to select the correct printer
    ' In this case the users have one of two PDF printers


    If txtPrinter = "Adobe PDF" Then
    Set Application.Printer = Application.Printers("Adobe PDF")
    Else 'Add other PDF printers here if Adobe PDF is not available
    Set Application.Printer = Application.Printers("CutePDF Writer")
    End If

    Exit_ChangeDefault2PDF:
    Exit Function

    Err_ChangeDefault2PDF:
    'MsgBox CStr(Err) & " " & Err.Description
    ChangeDefault2PDF = False
    Resume Exit_ChangeDefault2PDF

    End Function
    [/codebox]

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Attached is a stripped down version of what I used. NOTE that you will still need to get Stephan Lebans tool (http://lebans.com/reporttopdf.htm) and place the dll's (DynaPDF.dll, StrStorage.dll) in the directory.

    I hope this helps.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •