Send report to Acrobat with path & filename 1

[BryanEaton]

I need to send reports to Acrobat Writer, with a path and filename, then Acrobat to save to that path and file. I have a list of 160 reports I generate twice a month, each with a different filename which can be autogenerated.

I currently set the default printer to Acrobat and manually type in the filenames. This is somewhat timeconsuming and prone to error.

I found an example of chosing a printer programatically from "Access 97 Developer's Handbook" by Litwin, Getz, and Gilbert (Sybex) chapter 10, but this only covers printer things, not Acrobat things.

My next quest is to bring these reports together into one PDF file and bookmark them. Having read the Adobe site this can only be done in C++ which I have no knowledge about at all, my VBA is OK but limited.

Many thanks

 

[genomon]

First you need to own Acrobat Writer (not the free reader). We use a 3rd party library add-in (ACG PDF and Mail Library)and the following VBA:

Public Sub ProcessPDF()

Dim PDFObj As New PDFClass

With PDFObj
.ReportName = yourreport
.ImageType = "PDF"
.PDFNoShowPropDlg = True
.OutputFile = yourfile
.PDFEngine = 2 'Declare Distiller as print driver.
.printimage
End With

Set PDFObj = Nothing

I think I am prevented from directing you to the vendor website by the terms and conditions of this forum, but any good search engine can get you there. Good Luck!
-Geno

 

[BryanEaton]

Hello genomon,

Thank you. I have already got Acrobat 5 with PDF writer installed on my PC. I downloaded the ACG PDF library and installed it. It seems to work OK until I get clever and replace the strings with variables. It seems to get hung up - to the point of crashing Access - on the .OutputFile part.

This is my code:

Dim PDFObj As New PDFClass
Dim strReportName As String, strTargetName As String, strCondition As String, strFileName As String

strReportName = ListOfReports
strFileName = "Fred"
strFileName = strFileName & ".PDF"
strTargetName = """ & Me!TargetDirectory & strFileName & """

Debug.Print strReportName
Debug.Print strFileName
Debug.Print strTargetName

PDFObj.ReportName = strReportName

' If IsNull(Me![MyCondition]) = False Then
' strCondition = Me![MyCondition]
' Debug.Print strCondition
' PDFObj.ReportWhere = strCondition
' End If

' PDFObj.ImageType = "PDF"
PDFObj.PDFNoShowPropDlg = True
PDFObj.OutputFile = strTargetName '"C:\FICS\Fred.pdf"
PDFObj.PDFEngine = 1
PDFObj.printimage

Set PDFObj = Nothing

This is the error message I get:

[Run:Form]:MSACCESS.EXE - Application Error
The instruction at "0x77fcb032" referenced memory at "0x00000000". The memory could not be "written".
Click on OK to terminate the program

Ive tried the strTargetName with and without """ wrapped around it, the same error occurs either way.
It works just fine and dandy with the path and filename as text.


If Im doing something stupid, please let me know, otherwise Ill try my IT chaps to see if Ive installed the library OK.

Thanks

Bryan

 

[MoJoP]

Bryan

Where did you download the file from - I too have Adobe 5.0.

Thanks.

MoJoP

 

[BryanEaton]

I just did a Google search for "ACG PDF mail Library" and picked the first result.

 

[genomon]

Your file name is all icky. First of all you need to connect the string variables with the & sign. Also, the file destination has to be something the computer recognizes after you string all the variables together. An example : C:\YourFolder\YourPathname.pdf What you have glued together as the operand of the OutputFile method is coming out looking something like "&Me!Targetdirectory&strfilename&[space]C:\FICS\Fred.pdf". Just use the actual pathname, make sure that string variables are NOT enclosed in parens, and make sure you use the & sign to join the literals and variables. Good Luck! -Geno

 

[BryanEaton]

Hello Geno,

I had tried it that way, but it wouldnt work, so I tried all sorts of esoteric things. Getting back to basics has worked however, I removed all the crud and it now works - My IT man came down, said show me whats wrong, and it seemed to work - who knows, maybe hes a psycic or something!

Anyhow, thanks for your help.

All I need do now is have a bake sale to raise the funds to buy the ACG library.

Bryan

 

[BryanEaton]

Hey Genomon,

Having had success with printing to PDF, I now try to merge. Can you help with this please? I downloaded the Plus Pro version of the library, and replaced the standard one, and have registered it and removed the standard in Access. I tried what was in the sample and I have the same memory error as before. I have tried reboots, putting a timer loop in, checking the registry etc. All to no avail. The original part works fine, its just when it comes to merging it falls over now.

Heres the code:

Sub Ferm_onpen(report_tmple, Job_Class_Wanted, SiteCond, frmPath, MyAdmin, MySite, thisLoop)
On Error GoTo Err_Ferm_onpen_Click

Dim strFileName As String, strReportName As String, strTargetName As String
Dim strCondition As String, strReportTitle As String, intLoop As Integer

If MyAdmin = 0 Then 'Report being run by a normal user
DoCmd.OpenReport report_tmple, acViewNormal ', , SiteCond
Else 'Report being run by a Super User and therefore being printed to Acrobat

Dim PDFObj As New pdfclass
Dim strMasterDoc As String 'M
Dim objMerge As MergePDFClass 'M
Dim arrMerge() As String 'M
Dim dwReturn As Long 'M
Dim strBookmarkText As String 'M

ReDim arrMerge(0) 'M

intLoop = thisLoop
strReportName = report_tmple
strFileName = Job_Class_Wanted
strBookmarkText = Chr(124) & " " & strFileName
strFileName = MySite & strFileName & ".PDF"
strTargetName = frmPath & strFileName 'the path and filename of the new document, also the doc to merge
strTargetName = strTargetName & strBookmarkText
strCondition = SiteCond
strMasterDoc = frmPath & MySite & "Detail Report" & ".PDF" 'M The path and filename of the document to be merged into
strReportTitle = "PDFReportTitle" 'M

arrMerge(0) = strTargetName 'M

Debug.Print strReportName
Debug.Print strTargetName
Debug.Print strCondition
Debug.Print strMasterDoc
Debug.Print strReportTitle
Debug.Print "============="

Dim sngStart As Single, sngEnd As Single
Dim sngElapsed As Single

'=========================================
If 1 = 1 Then
If intLoop = 1 Then 'If this is the first loop through, create a document to hold the others. Make it a predefined report in Access.
With PDFObj
.ReportName = strReportTitle
.PDFNoShowPropDlg = True
.OutputFile = strMasterDoc
.PDFEngine = 1
.printimage
End With

Set PDFObj = Nothing

sngStart = Timer ' Get start time.
Do Until sngElapsed = 5 ' wait 5 seconds
sngEnd = Timer ' Get end time.
sngElapsed = Format(sngEnd - sngStart, "Fixed&quot ' Elapsed time.
Loop
End If

End If
'=========================================
With PDFObj 'Now each report gets created
.ReportName = strReportName

If IsNull(strCondition) = False Then
.ReportWhere = strCondition
End If

.PDFNoShowPropDlg = True
.OutputFile = strTargetName '"C:\FICS\Fred.pdf"
.PDFEngine = 1 '1 is PDF Writer (only one that works with Standard edition), 2 is Distiller (requires Pro version) print driver.
.printimage
End With

Set PDFObj = Nothing

sngStart = Timer ' Get start time.
Do Until sngElapsed = 5 ' wait 5 seconds
sngEnd = Timer ' Get end time.
sngElapsed = Format(sngEnd - sngStart, "Fixed&quot ' Elapsed time.
Loop

'=========================================

If 1 = 1 Then
Set objMerge = New MergePDFClass 'M now merge each report as it is created into the master document
With objMerge 'M

dwReturn = .MergePDFs(strMasterDoc, arrMerge()) 'M

End With 'M

Set objMerge = Nothing 'M

sngStart = Timer ' Get start time.
Do Until sngElapsed = 5
sngEnd = Timer ' Get end time.
sngElapsed = Format(sngEnd - sngStart, "Fixed&quot ' Elapsed time.
Loop


End If

'=========================================

End If 'MyAdmin = 0

Exit_Ferm_onpen_Click:
Exit Sub ' Function

Err_Ferm_onpen_Click:
MsgBox Error$
Resume 'Exit_Ferm_onpen_Click

End Sub

Thanks

Bryan
[BigEars]

 

[BryanEaton]

I noticed a couple of sillies in my code, but it still crashes:

strBookmarkText = Chr(124) & " " & strFileName
now
strBookmarkText = "| " & strFileName


strTargetName = strTargetName & strBookmarkText
now
strBookmarkText = strTargetName & strBookmarkText

arrMerge(0) = strTargetName
now
arrMerge(0) = strBookmarkText

each of the
Do Until sngElapsed = 5
now
Do Until sngElapsed >= 5

only minor changes, but it helps.

 

[khwaja]

Bryan

I was trying to find a similar solution as I have had to create a number of reports each month and to save it as a PDF file in a specfied directory. The only difference was that I needed to be able to print these reports for specdic regions regions which I can reflect as hard value in the code.

I am not very experienced in VB and would appreciate if you could kindly reproduce only relevant part of the code (dealing with printing and saving files)_incorporating changes you identified in a usable form so that I could benefit from this too. If you wish, you can email to on akhwaja@woolworths.com.au

Kind regards Cheers

AK