Print to PDF in VBA Code

[BlueWolverine]

Hello,
MS ACCESS 2003 on XP PRO.

I want to print documents to PDF, which I can do from my computer using the
printer "ADOBE PDF."

I have code that worked once to print the report to pdf, but now it's not
working.

Here's the short version of the code
DoCmd.OpenReport "r_SingleChecklist", acViewPreview
DoCmd.OpenReport "r_SingleChecklistSD", acViewPreview
Set rpt = Reports!r_SingleChecklist
Set rpt_not = Reports!r_SingleChecklistSD
'Open report
DoCmd.OpenReport rpt.Name, acViewPreview
'change Printer
Call m_Change_Printer(rpt, "Adobe PDF")
'Print Report
DoCmd.PrintOut acPages, , , acHigh, 1

Right now it fails on DOCMD.PRINTOUT.

The message is that the command isn't available right now.

Also, occasionally my change printer algorithm doesn't actually change the
printer.

Sub m_Change_Printer(r_report As Report, str_Printer_Name As String)

Dim prt_Adobe As Printer, prt_loop As Printer

For Each prt_loop In Application.Printers
If prt_loop.DeviceName = str_Printer_Name Then
With prt_loop
Set prt_Adobe = prt_loop
End With
End If
Next prt_loop
Set r_report.Printer = prt_Adobe


End Sub

Any idea?

 

[Alex Dybenko]

Hi,
suggest you to try this approach, work 100%
http://www.lebans.com/reporttopdf.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Jack Leach]

www.lebans.com/reporttopdf.htm


try that.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[BlueWolverine]

When using this function,

Where do the printed PDF files go???? I have tried everything I can. I
specified the full path name of the outputpdf I'm going for, using the folder
the mdb is in, and I cannot locate the files on my drive. I am not even sure
they exist.

Also, will printing forms to pdf in this manner trigger the ON FORM LOAD
event? I have code there that I need to worry about if it's not going to
trigger.

Thanks

 

[Jack Leach]

Where do the printed PDF files go????
They should be going to the exact path you specify (actually, they go to the
computer's temp folder, and are then copied to the specified path). Are you
sure you're using a fully qualified filepath, including the filename and .pdf
extension as the argument? Also, in the ConvertReportToPDF function, the 3rd
argument is the path, the second is the snapshot file which will probably be
left empty in your case. I assume you know, but doesn't hurt to check. If
you're doing both of those, you should have the files where you instructed
them. Double check your arg placements and make sure they're in the the
right spot.

Also, will printing forms to pdf in this manner trigger the ON FORM LOAD
event?

The code prints a report, which will fire all applicable report events in
the usual manner of printing or opening a report. This code does NOT print
forms... (at least not that I'm aware of... never tried, but I'm guessing its
a no).

If you are trying to print a form rather than a report and ON FORM LOAD
wasn't a typo, that's probably why you're not getting the file you expect.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[BlueWolverine]

I swear I'm doing this right but I can't find the files.

Here's the code.

str_pathName = CStr(Application.CurrentProject.path) & "\"
SaveAsDialogYesNo = False
str_filename = a_Tests(var_Item) & " - " & a_days(var_item1) & " - " & Date
& ".pdf"

blRet = ConvertReportToPDF("r_SingleChecklist", vbNullString, _
str_filename, SaveAsDialogYesNo,
True, 150, "", "", 0, 0, 0)

for the first time through this loop, str_fileName should be
"Duty Cycle - Monday - 4-23-2009.pdf"
Mouse over on str_ variables show they are what I'm expecting...

Am I doing something wrong?

 

[Jack Leach]

try removing the vbNullString variable from the second arg and leave it blank
instead... that's what I do. Assuming your variables are correct
"\\Folder\File.pdf" or "<Drive>:\File.pdf" are correct, I'm not sure where
else to look at quick glance.

hopefully removing the vbNullString will do it. I'm not exatcly sure how
Stephen handles the optional variables, and it may be causing a problem with
the function.


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[BlueWolverine]

That's a typo by the way, I meant a report. I AM ONLY WORKING REPORTS! to
clarify.

I mistyped. so if I have on report load code, it will trigger prior to the
PDF being made...cool. Thanks in that regard.

I still can't find the &^%$ing files. I know that I have tried filename.pdf
and path\filename.pdf and neither has put files in EITHER of the two places
they should go.

To confirm, the demo on lebans.com WORKS and I got that file exactly where
it was supposed to go.

Thanks, still need help!

 

[Jack Leach]

have you tried leaving the second argument blank instead of placing the
vbNullChar?? Notice all arguments are optional...

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[BlueWolverine]

I'll try this if my bug I found isn't the culprit. DATE returns as
MM/DD/YYYY which I'm sure you're aware can't be a filename.

I have a string converter now for mm-dd-yyyy.

We'll see I'll keep you posted.

PS vbnullwhateverthehellitis was what he used in the demo and it worked so I
dunno.

 

[Jack Leach]

PS vbnullwhateverthehellitis was what he used in the demo and it worked so I

dunno.

oh.. never noticed that, always left it blank myself.

And yes, the date might screw up your filename. Format(Datefield,
"YYYYMMDDHHMMSS") is what I use... then windows automatically sorts
everything by date for you


goodluck
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[BlueWolverine]

Everything is working now. The problem was the slashes in the datestamp.
Thanks!