UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Export Report To Pdf    
 
   
bg3075
post Mar 2 2011, 02:06 PM
Post #1

UtterAccess Member
Posts: 23
From: Texas



A previous co-worker created a VBA script, below, to export a report to PDF's. The problem is that it creates one single-page PDF for each record, as I would like to adjust to create one multi-page PDF, each page consisting of each record. Can anyone help please?
!--c1-->
CODE
Private Sub Command55_Click()
Dim dbs As Database
Dim rst As Recordset
Dim report As Recordset
Dim path As String
Dim count As Integer
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Well Maintenance Activity Table")
path = "D:\Temporary Logbook Files\"
With rst
    .MoveFirst
    DoCmd.RunCommand acCmdRecordsGoToFirst
    count = 1
    Do While Not .EOF
        FileName = path & "Well Maintenance Logbook Report " & Str(count) & ".pdf"
        
              
        DoCmd.OutputTo acOutputReport, "Well Maintenance Report", acFormatPDF, FileName, False
        DoCmd.RunCommand acCmdRecordsGoToNext
        count = count + 1
    .MoveNext
    Loop
    
    .Close
    
End With
End Sub
Go to the top of the page
 
+
theDBguy
post Mar 2 2011, 02:09 PM
Post #2

Access Wiki and Forums Moderator
Posts: 58,347
From: SoCal, USA



Hi Barry,
That version of Access are you using? If 2007, you could create a report that prints all the records but in separate pages. You can then just use the OutPutTo command to export the whole report as one PDF file.
Just my 2 cents...
Go to the top of the page
 
+
bg3075
post Mar 2 2011, 03:58 PM
Post #3

UtterAccess Member
Posts: 23
From: Texas



Tried...
To print each record on a separate page, double-click the section selector (section selector: The box on the left side of a section bar when an object is open in Design view. Use the box to perform section-level operations, such as selecting the section.) of the detail section (detail section: Used to contain the main body of a form or report. This section usually contains controls bound to the fields in the record source but can also contain unbound controls, such as labels that identify a field's contents.), and set the ForceNewPage property to After Section.
sed macro to use the OutputTo command on the report. It only printed the header, one page. It works correctly when selecting the table as the object, but not when selecting the report. I guess will have to do it in VBA only.
Go to the top of the page
 
+
theDBguy
post Mar 2 2011, 04:05 PM
Post #4

Access Wiki and Forums Moderator
Posts: 58,347
From: SoCal, USA



Hi Barry,
nother way to create separate pages for each record is to add Grouping to the report and then Force a New Page for each group. You may not need any VBA at all.
Just my 2 cents...
Go to the top of the page
 
+
bg3075
post Mar 2 2011, 04:43 PM
Post #5

UtterAccess Member
Posts: 23
From: Texas



I think this may work (from "CautionMP" in Tek-Tip Forums), if I could figure out how to adjust the path of the second source to be a wildcard search in the directory, therefore selecting all additional PDF, created above, to merge. Then, I would like to delete all files (PDFs) in the same directory, but the merged one (which should be easy).
quot;CautionMP (Programmer) 30 Aug 06 15:59
Have I mentioned recently how much I don't like the Adobe?Anyway, had some spare time so here is a rough shell that should do the trick. I combined two 2 page documents to create a new 4 page document and it took about 12 seconds. I suspect that the majority of that time was spent creating the two AcroExch.PDDoc objects so your code should run faster, but not fast."
CODE

Sub TestCombinePDF()
'Relies on the Adobe Acrobat 6.0 Type Library
Dim objCAcroPDDocDestination As Acrobat.CAcroPDDoc
Dim objCAcroPDDocSource As Acrobat.CAcroPDDoc
'Initialize the objects
Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")
Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")
'Open Destination, all other documents will be added to this and saved with
'a new filename
objCAcroPDDocDestination.Open ("C:\Source1.pdf")
'Do your loop here to open subsequent documents that you want to add
'Do
'Open the source document that will be added to the destination
objCAcroPDDocSource.Open ("C:\Source2.pdf")
If objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
'-1 Success
Else
'0 problem
End If
objCAcroPDDocSource.Close
'loop
objCAcroPDDocDestination.Save 1, "C:\Destination.pdf"
objCAcroPDDocDestination.Close
Set objCAcroPDDocSource = Nothing
Set objCAcroPDDocDestination = Nothing
End Sub
I have tried replacing "objCAcroPDDocSource.Open ("C:\Source2.pdf")" with "objCAcroPDDocSource.Open ("C:\" & "*.pdf")". This causes no error, but does not complete the merge either.
Go to the top of the page
 
+
bg3075
post Mar 3 2011, 05:40 PM
Post #6

UtterAccess Member
Posts: 23
From: Texas



Here is another approach I've taken. An error is now returning "For without Next" at the End Sub line. I cannot find out why. I have the statements "For I=..." and "Next I", which appear to be in the correct locations to me.
CODE
Private Sub Command55_Click()
im dbs As Database
Dim rst As Recordset
Dim report As Recordset
Dim path As String
Dim count As Integer
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Well Maintenance Activity Table")
path = "D:\temp\"
With rst
    .MoveFirst
    DoCmd.RunCommand acCmdRecordsGoToFirst
    count = 1
    Do While Not .EOF
        FileName = path & "Well Maintenance Logbook Report " & Str(count) & ".pdf"
        DoCmd.OutputTo acOutputReport, "Well Maintenance Report", acFormatPDF, FileName, False
        DoCmd.RunCommand acCmdRecordsGoToNext
        count = count + 1
    .MoveNext
    Loop
    .Close
End With
Dim varFileArray As Variant
    Dim lngI As Long
    Dim strDirName As String
    Dim origPdfDoc As Acrobat.CAcroPDDoc
    Dim newPdfDoc As Acrobat.CAcroPDDoc
    Dim path2 As String
    Dim varNewTotalPages As Long
    Dim varOldTotalPages As Long
        
    Const NO_FILES_IN_DIR As Long = 9
    Const INVALID_DIR As Long = 13
    
    strDirName = "D:\temp"
    varFileArray = GetAllFilesInDir(strDirName)
    
    'path = ctlpathname & varFileArray(1) & ".pdf"
    FileName = path
    NewFileName = strDirName & "Logs.pdf"
    FileCopy FileName, NewFileName
    path = NewFileName
    
    For lngI = 0 To UBound(varFileArray)
        Debug.Print varFileArray(lngI)
        
Set origPdfDoc = CreateObject("AcroExch.PDDoc")
If origPdfDoc.Open(path) Then
    For I = 2 To UBound(varFileArray, 1)
        path2 = strDirName & varFileArray(I) & ".pdf"
        varOldTotalPages = origPdfDoc.GetNumPages - 1
        
        Set newPdfDoc = CreateObject("AcroExch.PDDoc")
        
        If newPdfDoc.Open(path2) Then
            'Get total # of pages too insert
            varNewTotalPages = objCAcroPDDocDestination.GetNumPages
            'Insert pages into original pdf
            origPdfDoc.InsertPages varOldTotalPages, objCAcroPDDocDestination, 0, varNewTotalPages, False
            'Save doc
            origPdfDoc.Save PDSaveIncremental, path
        newPdfDoc.Close
        Kill path2
  
        End If
        
    Next I
Else
End If
origPdfDoc.Close
Set origPdfDoc = Nothing
Set newPdfDoc = Nothing
Kill FileName
End Sub
Go to the top of the page
 
+
bg3075
post Mar 7 2011, 11:55 AM
Post #7

UtterAccess Member
Posts: 23
From: Texas



And tried this, which creates the "Destination.pdf" but only includes the first document page.
CODE
Dim objCAcroPDDocDestination As Acrobat.AcroPDDoc
Dim objCAcroPDDocSource As Acrobat.AcroPDDoc
im strDocPath As String
Dim strCurrentFile As String
strDocPath = "d:\temp\"
strCurrentFile = Dir(strDocPath & "*.pdf")
''Initialize the objects
Set objCAcroPDDocDestination = CreateObject("AcroExch.PDDoc")
Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")
'
'Open Destination, all other documents will be added to this and saved with
'a New FileName
objCAcroPDDocDestination.Open ("D:\temp\Source1.pdf")
'
'Do your loop here to open subsequent documents that you want to add
'Open the source document that will be added to the destination
Do While strCurrentFile <> ""
objCAcroPDDocSource.Open (strCurrentFile)
If objCAcroPDDocDestination.InsertPages(objCAcroPDDocDestination.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
'-1 Success
Else
'0 problem
End If
strCurrentFile = Dir
Loop
objCAcroPDDocSource.Close
objCAcroPDDocDestination.Save 1, "D:\temp\Destination.pdf"
objCAcroPDDocDestination.Close
Set objCAcroPDDocSource = Nothing
Set objCAcroPDDocDestination = Nothing
End Sub
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 21st October 2014 - 02:01 AM