Automated reports to Acrobat

[sha7jpm]

Hi,

After much frustration and hair pulling I have managed to get the code below to work.. It prints out reports from access one at a time cycling through the list of schools in our county. My question is that I would like to save each report with a filename eg KS1_3000_version1.pdf where 3000 is the unique school ID.

So my question is how can I get the code/acrobat to save my file in a desired location with a pre-specified filename?

this would make the whole process a real click of a button! I know it is possible because we have a national database that does the same thing.. but I cannot crack the backend to see the code

bah!

thanks in advance

john

***********

Option Compare Database

Private Sub Command1_Click()
Dim repQuery As QueryDef
Dim dBase As Database
Dim rsRep As DAO.Recordset
Dim strrep As String
Dim data1 As String

Set dBase = CurrentDb()
Set repQuery = dBase.QueryDefs("john_test_ks1")
Set rsRep = CurrentDb.OpenRecordset("2_KS1_Performance_review_report")

Do While Not rsRep.EOF
data1 = rsRep.Fields("ESTAB_FK").Value
repQuery.sql = "SELECT SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA.DFES, * FROM 2_KS1_Performance_review_report INNER JOIN SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA ON [2_KS1_Performance_review_report].ESTAB_FK = SCHOOL_BASE_DATA_SCHOOL_BASIC_DATA.DFES WHERE ((([2_KS1_Performance_review_report].ESTAB_FK)= " & data1 & "));"
repQuery.Close
DoCmd.OpenReport "john_test_KS1_report"
DoCmd.Close acReport, "john_test_KS1_report"
MsgBox "done"
rsRep.MoveNext
Loop

Set rsRep = Nothing

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

 

[The_Doc_Man]

The only way to do this requires that you have a version of Acrobat that "exposes" its controls to MS Automation (a.k.a. ActiveX). Having never found such an animal myself, I cannot give you details. However, in general, here's how to tell whether you have anything that will help.

Assuming you have Acrobat Writer or Composer or whatever they call the version you have, you must check that version's Help files for topics dealing in automation, Visual Basic (straight up or VB for Applications). If you do not have a VB / VBA category, check the manual for topics such as ActiveX or Automation.

If you do not have a version of Acrobat that exposes its controls via the Component Object Model (COM), you cannot do what you want. Ever. If you DO have such a version, read ITS help files first to see how to manipulate documents. Then read Access help on Application Objects, because to create that report, you will need to create an app object for Acrobat.

If this is even possible, Acrobat will expose controls that let you add pages, paragraphs, text, images, diagrams, etc. to some collection or another. They will probably have their own name for the collection. In Word, this collection would be the Documents collection, which would have an ActiveDocument pointer leading to various interesting collections such as paragraphs, tables, etc. You would then be able to set values to various controls in the members of the collection. Controls for a paragraph collection might include Font, TextColor or ForeColor, etc.

If I recall correctly, several folks in this forum have posted Word app code. While the exact code won't work for Acrobat, it would be a good idea to see how you do such a thing in Word. The Acrobat controls won't be that different.

 

[Oldsoftboss]

Not as complicated as doc suggests, but still requires you to have a version of Acrobat that will create PDF files. When I installed Actrobat, I installed Acrobat PDFWriter. This I presume is the printer driver that creates the PDF file. Once installed it is simply a matter of selecting the PDFWriter as the destination of your report. (It will be in the list of installed printers) It will then ask for the file name (sure that can be animated) and convert it to a PDF file.

Dave

 

[The_Doc_Man]

Oldsoftboss is describing a Pseudo-driver that can build files in an alternate format. You would build the complete file but then PRINT it to an alternate printer. Not as bad, but it does require you to build the complete report first. If I recall correctly, Word Perfect also can do this. I forget the name of the pseudo-printer driver, but after you install WP, you see an extra printer in your list of possible printers.

Search the forum for the topic on "selecting an alternate printer" for code that does this step.

 

[Oldsoftboss]

I was under the impression from the code in the first post that a report was being generated.

Dave

 

[sha7jpm]

cheers!

Dear All,

Many thanks for the responses, it is a maddening situation.. basically though OldSoftBoss (Dave) is right, the report runs and calls acrobat writer, but it is at this point that I am stuck.. someone still has to sit and type in the school unique id number into each report filename and click on save. If only i could automate this so that it knows the filename to place in the save as dialog box...

then the whole routine would be completely automatic and a folder would exist with all the exported files.

an ideal world!!

ta

John

 

[kleblanc]

Hello,

I know it may be over do but here is how I solved a similar problem.

Module Code:

Option Compare Database
Option Explicit

Declare Function aht_apiWriteProfileString Lib "kernel32" Alias "WriteProfileStringA" (ByVal strAppName As String, ByVal strKeyName As String, ByVal strValue As String) As Integer

Sub ChangePdfFileName(NewFileName As String)
Call aht_apiWriteProfileString("Acrobat PDFWriter", "PDFFileName", NewFileName)
End Sub

Code behind my command button:

Private Sub PrintPdf_Click()
Dim PdfFileName As String

PdfFileName = Me.txtPdfFileName & ".pdf"

ChangePdfFileName PdfFileName
DoCmd.OpenReport "invoice", acViewNormal

End Sub

That will give you the new name per report.

To stop the Adobe print dialog you need to set the properties of the PdfWriter.

Change the Adobe Outup Folder from Prompt for Adobe Pdf Name to a predefined folder.

You can also turn off the preview document after creation in the printing preferences.

Keith