Solved

Print access report to postscript (or really, ANY) file

Posted on 2006-07-04
16
1,147 Views
Last Modified: 2013-12-03
Hi,

It seems to me Access is making it a real pain to print to file from vba..
The goal is to convert an Access report to .pdf format.  
I automated ghostscript for .ps to .pfd conversion, got a PostScript driver, and am now stuck at what i thought would be the easiest step - printing to a [.ps] file from Access VBA.

Looked at DEVMODE - it seems it specifies all params but output redirection.. Any ideas?

Any comments are appreciated..

0
Comment
Question by:mokn
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17040254
You would most likely need some s/w to convert ps to pdf
e.g. http://www.ps2pdf.com/convert/index.htm

alternatively u can use something like this   pdf995

and u can code the ini file to specify a file therefore it prints to a pdf without prompting


It modifies pdfsync.ini, it will be held where pf995 is installed
call   the function pdfwrite specifying the name of the report (reportname), destpath (where u want it written to), criteria for report
This code will open the report for u

Have a look at the code


Sub pdfwrite(reportname As String, destpath As String, Optional strcriteria As String)



Option Compare Database
Option Explicit

'Read INI settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
   "GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
   ByVal lpKeyName As Any, ByVal lpDefault As String, _
   ByVal lpReturnedString As String, ByVal nSize As Long, _
   ByVal lpFileName As String) As Long

'Write settings
Declare Function WritePrivateProfileString Lib "kernel32" Alias _
   "WritePrivateProfileStringA" (ByVal lpApplicationName As String, _
   ByVal lpKeyName As Any, ByVal lpString As Any, _
   ByVal lpFileName As String) As Long

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub pdfwrite(reportname As String, destpath As String, Optional strcriteria As String)

' Runs an Access report to PDF995 to create a pdf file from the report.
' Input parameters are the name of the report within the current database,
' the path for the output file, and an optional criteria for the report

' Be sure to check that the "Generating PDF CS" setting in pdfsync.ini is set to 0
' when pdf995 is idle. This codes uses that as a completion flag as it seems to be
' the most reliable indication that PDF995 is done writing the pdf file.


' Note: The application.printer object is not valid in Access 2000
' and earlier. In that case, set the printer in the report to pdf995
' and comment out the references herein to the application.printer

Dim syncfile As String, maxwaittime As Long
Dim iniFileName As String, tmpPrinter As Printer
Dim outputfile As String, x As Long
Dim tmpoutputfile As String, tmpAutoLaunch As String

' set the location of the PDF995.ini and the pdfsync files
iniFileName = "C:\Program Files\pdf995\res\pdf995.ini"
syncfile = "C:\Program Files\pdf995\res\pdfsync.ini"

' build the output file name from the path parameter and the report name
'If Mid(destpath, Len(destpath), 1) <> "\" Then destpath = destpath & "\"
If LCase$(Right$(destpath, 4)) = ".pdf" Then
    outputfile = destpath '& reportname & ".pdf"
Else
    outputfile = destpath & ".pdf"
End If

' PDF995 operates asynchronously. We need to determine when it is done so we can
' continue. This is done by creating a file and having PDF995 delete it using the
' ProcessPDF parameter in its ini file which runs a command when it is complete.

' save current settings from the PDF995.ini file
tmpoutputfile = ReadINIfile("PARAMETERS", "Output File", iniFileName)
tmpAutoLaunch = ReadINIfile("PARAMETERS", "Autolaunch", iniFileName)

' remove previous pdf if it exists
On Error Resume Next
Kill outputfile
On Error GoTo Cleanup

' setup new values in PDF995.ini
x = WritePrivateProfileString("PARAMETERS", "Output File", outputfile, iniFileName)
x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", "0", iniFileName)

' change the default printer to PDF995
' if running on Access 2000 or earlier, comment out the next two lines
Set tmpPrinter = Application.Printer
Application.Printer = Application.Printers("PDF995")

'print the report
DoCmd.OpenReport reportname, acViewNormal, , strcriteria

' cleanup delay to allow PDF995 to finish up. When flagfile is nolonger present, PDF995 is done.
Sleep (10000)
maxwaittime = 300000 'If pdf995 isn't done in 5 min, quit anyway
Do While ReadINIfile("PARAMETERS", "Generating PDF CS", syncfile) = "1" And maxwaittime > 0
    Sleep (10000)
    maxwaittime = maxwaittime - 10000
Loop

' restore the original default printer and the PDF995.ini settings
Cleanup:
Sleep (10000)
x = WritePrivateProfileString("PARAMETERS", "Output File", tmpoutputfile, iniFileName)
x = WritePrivateProfileString("PARAMETERS", "AutoLaunch", tmpAutoLaunch, iniFileName)
x = WritePrivateProfileString("PARAMETERS", "Launch", "", iniFileName)
On Error Resume Next

' if running on Access 2000 or earlier, comment out the next line
Application.Printer = tmpPrinter

End Sub

Function ReadINIfile(sSection As String, sEntry As String, sFilename As String) As String
Dim x As Long
Dim sDefault As String
Dim sRetBuf As String, iLenBuf As Integer
Dim sValue As String

'Six arguments
'Explanation of arguments:
'sSection: ini file section (always between brackets)
'sEntry : word on left side of "=" sign
'sDefault$: value returned if function is unsuccessful
'sRetBuf$ : the value you're looking for will be copied to this buffer string
'iLenBuf% : Length in characters of the buffer string
'sFileName: Path to the ini file

sDefault$ = ""
sRetBuf$ = String$(256, 0)   '256 null characters
iLenBuf% = Len(sRetBuf$)
x = GetPrivateProfileString(sSection, sEntry, _
           sDefault$, sRetBuf$, iLenBuf%, sFilename)
ReadINIfile = Left$(sRetBuf$, x)

End Function
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17040982
the code example I gave above is slightly tweaked. You can find the original here
http://www.freeware995.com/misc/vbacode.txt


If u have Adobe Acrobat  http://www.mvps.org/access/reports/rpt0011.htm

there is this, dont know how well it will suit u though  http://zipguy.012webpages.com/freepdf.htm

some more info here  http://www.vb123.com/toolshed/links/pdfwriters.htm

0
 
LVL 1

Author Comment

by:mokn
ID: 17041447
Thank you for your input, rockiroads, I really appreciate it.. But as I said, I already have ghostscript automated, which took a sufficient amount of labor, plus, I really like the fact that I can customize it to the fullest, and this thing (pdf995) is built on it..

I would still love to know how I could simply print to a file from access vba.  

Thanks in advance
0
 

Expert Comment

by:altiplano
ID: 17042091
You could try the following:

Add a new printer in Windows (Start>Setting>Printers and Faxes>Add Printer)
- Select "Local printer" and remove check for  "Automatically detect..."
- Click Next
- "Use the following port: FILE: (Print to File)
- Select a driver for a PS printer. You'll have to experiment a bit to find the best driver for your situation. HP Color LaserJet PS is probably OK.
- Finish the installation and give it a meaningfull name.

Now if you print to this printer, you'll be prompted for a location and filename. This will be the tricky part as you'll have to train your users to put in here the right location and de extention .ps.

regards,
Michiel
0
 
LVL 1

Author Comment

by:mokn
ID: 17042118
Thank you Michiel, I appreciate your comment.  

I know how to print to a file via win dialog :)

As I stated above, I am trying to do that via ACCESS VBA.  

:)
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17042850
mokn,
Access VBA incorporates calls to win dialog statements where needed.  Rocki gave you the module code to print to a pdf995 driver via Access vba (see excerpt below), which you would have to modify/adapt to the specs of your pdf driver.

Private Sub pdfwrite(reportname As String, destpath As String, Optional strcriteria As String)

' Runs an Access report to PDF995 to create a pdf file from the report.
' Input parameters are the name of the report within the current database,
' the path for the output file, and an optional criteria for the report

.............................................................................................

................................................................................................

End Sub
0
 
LVL 1

Author Comment

by:mokn
ID: 17042953
Thanks for your reply, puppydogbuddy

I've investigated pdf995 and it does work, the only issue is the ad popping up on each invocation.  I also know that it is a small price to pay for this utility, but after having spent a good amount of time time on building and customizing ghostscript, I am refusing to pay any amount to a 3rd party.

There has got to be away to print to a file from access vba, which is what I am looking to resolve in this post.  

Again, fully aware of other tools/utilities, but what I need is a <<<<WAY TO PRINT TO A FILE FROM ACCESS VBA>>>>
 
From my research so far it seems the only way to do so is via Win API, but if anyone knows of a quick and easy way to do so, please let me know.

Again, thank you all for your suggestions.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17043123
mokn,
I was not suggesting that you use the pdf995 driver.  What I was trying to tell you is that the code needed depends on the parameters and code contained in the specs supplied with your pdf driver.  There is no generic statement in Access that works for all pdf printers.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 500 total points
ID: 17070676
mokn,
See this link for a solution you might like.  The Lebans code only requires you to set up a function in a standard module, and call that function when the print command is issued.  I have recommended this to several people, and all of them said it was easy to implement and that they were very satisfied:

               http://www.experts-exchange.com/Databases/MS_Access/Q_21902611.html
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17070684
PS: See the final comment from the asker in the post for the details of the solution.
0
 
LVL 1

Author Comment

by:mokn
ID: 17072411
puppydogbuddy , I could hug you.

Thanks so much, exactly what I was looking for - I get to keep my setup - ps -> gs and got the missing link.
Awesome!

0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17072714
You are welcome.  Glad I could help.
0
 
LVL 1

Author Comment

by:mokn
ID: 17073779
For anyone who's curious, the answer to the original question is configure a postscript printer - add a local port and set it to the filename, to default it to print to a file.. That was the part I was missing :)

With ghostscript automated everything else is literally 3 lines of code - set printer to the PostScript Printer, open report, and call gs...

Thank you all for your excellent comments.

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17077339
Nice find PDP
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17077357
Rocki,
Thanks.....but PDP? I stay away from that stuff (LOL)!!!  It is PDB to you.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17077382
Ah, sorry mate
typo
Its late u know, Im off to bed now as its well past my bedtime
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article discusses the PaperPort 14 Scanner Connection Tool, which Nuance provides at no charge in order to fix scanning problems in Windows 8. Furthermore, users of PaperPort 14 in Windows 7 and Windows 10 have reported that the tool works in t…
This article was inspired by a question here at Experts Exchange (http://www.experts-exchange.com/Software/Photos_Graphics/Images_and_Photos/Q_28629170.html). The requirements stated in that question are (1) reduce the file size of a large number of…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now