Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2002
    Posts
    64

    Unanswered: Report to PDF (Access 2K)

    I've got the Adobe Writer, it's fab. I'm trying to send my monthly reports through Adobe and save them as lovely PDF files, but I don't know where to start.

    I have a routine that outputs a query to Excel as such:
    Dim db As Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Quantity Salesman")

    Do While Not rs.EOF
    [Salesman] = rs!Salesman
    DoCmd.OutputTo acOutputQuery, "Quantity Report Make
    Spreadsheet", acFormatXLS, _
    "C:\Database\Salesman\" & Format([Forms]![Menu]!
    [ToDate], "mm-yy") & " " & _
    [Forms]![Menu]![Salesman] & ".xls", False
    rs.MoveNext
    Loop


    I'd like to do the same sort of thing through Adobe. Hope that makes sense, and any tips gratefully accepted!

  2. #2
    Join Date
    Sep 2003
    Posts
    7

    Re: Report to PDF (Access 2K)

    I have on my task list the same objective. This is my plan but I haven't actually done it yet:

    Instead of outputing the query/report, set the default printer to the PDF writer and then printout the query/report. I think you should be able to set/change the default printer in the procedure and then print the object you wish.

    See this KB article regarding changing the default printer using code:
    http://support.microsoft.com/default...NoWebContent=1

    Hope this helps.
    OM Gang


    Originally posted by TJoy
    I've got the Adobe Writer, it's fab. I'm trying to send my monthly reports through Adobe and save them as lovely PDF files, but I don't know where to start.

    I have a routine that outputs a query to Excel as such:
    Dim db As Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Quantity Salesman")

    Do While Not rs.EOF
    [Salesman] = rs!Salesman
    DoCmd.OutputTo acOutputQuery, "Quantity Report Make
    Spreadsheet", acFormatXLS, _
    "C:\Database\Salesman\" & Format([Forms]![Menu]!
    [ToDate], "mm-yy") & " " & _
    [Forms]![Menu]![Salesman] & ".xls", False
    rs.MoveNext
    Loop


    I'd like to do the same sort of thing through Adobe. Hope that makes sense, and any tips gratefully accepted!

  3. #3
    Join Date
    Feb 2002
    Posts
    64
    Thanks for the reply.

    Sorry, I'll explain my question better.

    I don't have a problem getting my reports to PDF. My problem is figuring out how to automate the process so I can open my reports, output them to PDF using the writer, then save the reports with a name based on certain criteria passed to a form. Similar to the process I used with my query to Excel files in the code I posted above.

    Hope that clarifies more what I am trying to achieve.

  4. #4
    Join Date
    Feb 2002
    Posts
    64
    I found a clever way to use default settings in Acrobat Distiller and the FileCopy function in Visual Basic to get this job done.

    In other words, never mind!

  5. #5
    Join Date
    Sep 2003
    Posts
    17
    Originally posted by TJoy
    I found a clever way to use default settings in Acrobat Distiller and the FileCopy function in Visual Basic to get this job done.

    In other words, never mind!
    Would you mind attaching your updated code for others to share also.

    I too use Acrobat but automation has always been a problem.
    Thanks in advance
    Dave

  6. #6
    Join Date
    Feb 2002
    Posts
    64
    Well, there were several steps to the "process". Probably not the most efficient way, but this was a work in progress and I can always tidy it up a bit.

    1) I setup my PDF port as my working/default directory.
    2) I setup the Adobe Distiller defaults to save and overwrite without prompting.
    3) I setup my reports to use Adobe Distiller as the Specific Printer.
    4) I ran a macro which opened the reports in Print view, sending them in PDF format to my working/default directory.
    5) I called this function to copy them to where I needed them (only one report is listed, I'll add the rest):

    Function CopyPDFs() As String

    Dim SourceFile As String
    Dim DestinationFile As String
    Dim FolderName As String
    Dim FSO

    Set FSO = CreateObject("scripting.filesystemobject")

    FolderName = Left([Forms]![Menu]![FromFormat], 4) & "-" & Mid([Forms]![Menu]![FromFormat], 5, 2)

    If Not FSO.folderexists("c:\" & FolderName) Then
    MkDir ("c:\" & FolderName)
    End If

    SourceFile = "c:\Default Folder\Monthly Report.pdf"
    DestinationFile = "c:\" & FolderName & "\" & _
    Left([Forms]![Menu]![FromFormat], 4) & "-" & Mid([Forms]![Menu]![FromFormat], 5, 2) & _
    " Monthly Report.pdf"

    FileCopy SourceFile, DestinationFile

    End Function

    Hope this helps!

  7. #7
    Join Date
    Oct 2003
    Posts
    13
    I did exactly this but didn't have to copy files.

    You can specify the destination folder when creating a new port.

    I wrote the instructions in detail. Go to www.adobe.com

    Then Support, Forums, PDF Workflow.

    Near the top are 2 threads about MSAccess2000.
    All the instructions are there.

    My problem is programatically merging 2 reports into 1 pdf file.

    There's a program called PDF-XChange. I downloaded the demo version and it works perfectly. Create a PDF file and then append a second one to it.

    I'm a little hesitant to use it though.

Posting Permissions

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