Access to PDF (Access2003)

Topic

New Reply

I found code that I really like and would like to use…however at the end I am getting an empty .ps file

Here is something I really like:

Dim objPDF as PdfDistiller,strRepName as String,strCustomRepName as String, strPath as String, intRetvalue as Integer

strRepName=”Inventory”

strPath=”C:PDFFILEs”

strCustomRepName=”Inventory_Report__Month_May”

Set Application.Printer=Application.Printers(“Adobe PDF”)

set objPDF = New PdfDistiller

intRetValue=objPDF.FileToPDF (strRepName,strPath & strCustomFileName & “.pdf”,””)
******************************************
Sub Generate_PS(strReportName as String) ‘ Generates a post script file

DoCmd.OpenReport strReportName,AcViewPreview
SendKeys “%Fp%l~” & strpath & strReport & “.ps~”,True
DoCmd.Close acReport,strReportName

End Sub
*******************************************

I am however getting an empty Distiller file.

In my adaptation of this code – it looks as follows

strRecruitersNames = !NAME_DISPLAY
ReportName = FileName & ” ” & strRecruitersNames & “_” & FileDateFormat
ReportLocation = “C:My Documents”

DoCmd.OpenReport ReportName:=”Candidate_Report”, View:=acViewPreview, WhereCondition:=”Name_Display = ” & Chr(34) & strRecruitersNames & Chr(34)

SendKeys “%Fp%l~” & strpath & strReport & “.ps~”,True
DoCmd.Close acReport,strReportName

Set Application.Printer=Application.Printers(“Adobe PDF”)

set objPDF = New PdfDistiller

intRetValue=objPDF.FileToPDF (ReportName, ReportLocation & “.pdf”, “”)
********************************************************************************

So my Access Report is open and I am going to the line SendKeys “%Fp%l~” & strpath & strReport & “.ps~”,True
and I see the flash of the directory and print…and no file gets created.

Do you see where I am failing?

Thanks

Reply | Quote

Replies

(Edited by HansV to make URL clickable – see Help 19)

I found the thread in another forum where they have a perfect simple code for Excel to the PDF.

http://www.tek-tips.com/viewthread.cfm?qid=416162%5B/url%5D

what we need is the Active Printer (property we don’t have in Access) be set to Distiller.

Anyone, please?

Reply | Quote

You can do something like this:

DoCmd.OpenReport ReportName:=”Candidate_Report”, …
Reports(“Candidate_Report”).Printer = Application.Printers(“Adobe Distiller”)

Replace “Adobe Distiller” with the actual name of the Distiller printer.

Reply | Quote

It is great, no one could figure it out and you did it again, Hans, thanks!

However I am stil in a jam.
I am using Adobe 8 Professional.
Somehow we do not have Distiller anymore.
Everything was coded previously is failing and searching on Internet for 2 days I found that I am not alone.
I found the article with name:
Print Access Report to PDF V6 or V7, using VBA – is this a secret?
Where person struggling with the issue and posting pretty good code he used in the past but it is not working no more.

I am also asking – is this a secret? Which is funny, right?

I don’t know where else to go, thanks to all and please, I am open to an ideas.

Reply | Quote

If you have Adobe Acrobat, you can usually print to the “Adobe PDF” printer – check your printer list for the exact name.

Reply | Quote

I use the ConvertReportToPDF function I got from http://www.lebans.com[/url%5D. Easy to implement.

Reply | Quote

I already pointed MS_Fan to ReportToPDF in post 733,173 but apparently (s)he doesn’t want to use it.

Reply | Quote

Gentleman,
I appreciate your help and determination and I want to apologize to Hans – I did not mean to ignore your suggestion – it is just I use to be able to do this in a snap and Leban’s code was so scarily complicated…
However I have adapted it to run my Reports and output into PDF and it seems working just great!!!
Thanks for that.

One thing is with saving actual PDF file in certain directory:

With rstRS
Do While Not .EOF
strRecruitersNames = !NAME_DISPLAY
ReportName = “Candidate_Report”
ReportLocation = “C:My Documents”

DoCmd.OpenReport ReportName:=”Candidate_Report”, View:=acViewPreview, WhereCondition:=”Name_Display = ” & Chr(34) & strRecruitersNames & Chr(34)

ReportNewName = FileName & ” ” & strRecruitersNames & “_” & FileDateFormat

blRet = ConvertReportToPDF(ReportName, vbNullString, ReportNewName & “.PDF”, True)

DoCmd.Close acReport, “Candidate_Report”

DoCmd.Save
.MoveNext

Loop

End With
***********************************************************************

After I am closing Access Report – I have PDF is still open.

I need to Save it in C: somewhere and close it.

The rest is working and I have to thanks you again and again – both of you, Hans and Mark!
Have a great day!

Reply | Quote

Stephen Lebans’ code is complicated but you don’t have to worry about that – you don’t have to understand it. You only need to use the ConvertReportToPDF function.

You can specify that the PDF file created by the function isn’t opened by adding False for the StartPDFViewer argument:

blRet = ConvertReportToPDF(ReportName, vbNullString, ReportNewName & “.PDF”, True, False)

Reply | Quote

I’ve realized that and I’ve took a chance…after you basically made me do it 🙂 thanks

I did changed False to the True and now it is opening PDF.

However now if I change it back – how do I save it to the C:?

I don’t see any place to enter directory.

Thanks

Reply | Quote

You can include the path in the 3rd argument, where you now have

ReportNewName & “.PDF”

You could change this to

“C:” & ReportNewName & “.PDF”

Modify as needed.

Reply | Quote

Hm! That easy, huh?

Thanks so much and I hope I am done with questions!

Yep and it is ‘she’:-)

Reply | Quote

I’ve jinxed myself!

Somewhere in the code

Public Property Let DialogTitle(vNewValue As String)
‘assign object’s DialogTitle property
sDialogTitle = vNewValue
End Property

it goes to the place below

clsDialog.Filter = “PDF (*.PDF)” & Chr$(0) & “*.PDF” & Chr$(0)
‘clsDialog.Filter = clsDialog.Filter & “Gif (*.GIF)” & Chr$(0) & “*.GIF” & Chr$(0)
‘clsDialog.Filter = “ALL (*.*)” & Chr$(0) & “*.*” & Chr$(0)
clsDialog.hDC = 0
clsDialog.MaxFileSize = 256
clsDialog.Max = 256
clsDialog.FileTitle = vbNullString
clsDialog.DialogTitle = “Please Select a path and Enter a Name for the PDF File”
clsDialog.InitDir = vbNullString
clsDialog.DefaultExt = vbNullString

it has “Please Select a path and Enter a Name for the PDF File” and window pops up waiting for me to select where to output the file.

I need to bypass this or give it a name and the location which I thought I need to do later…

However in a mean time I am entering name of the file and it gets saved into directory but I am receiving a message about file bienf corrupted and try again…
but file is saved and fine and sitting where it is suppose to be.

Was I suppose to not to copy some of the modules?
I took it all!

Reply | Quote

Please take a look at the description of the arguments for ConvertReportToPDF in the code (in the modReportToPDF module).
If you don’t want the Save As dialog to be displayed, you should specify False for the 4th argument ShowSaveFileDialog.

blRet = ConvertReportToPDF(ReportName, vbNullString, “C:” & ReportNewName & “.PDF”, False, False)

Reply | Quote

WORKS WORKS WORKS!
THANKS THANKS THANKS!

Reply | Quote