Stephen Lebans Report2PDF

[Silvester]

Hi,

With reference to Stephen Lebans interesting report to PDF solution found at
http://www.lebans.com/reporttopdf.htm

If reference is set to the provided mde and the app is distributed along
with the mde & dll in the app's default directory, will I run into broken
reference issues on end user machines?

Basically I'm asking how to set up references to this mde so that it can be
safely distributed.

TIA

 

[Ron Weiner]

Stephen

This post lead me to your site to have a look at your solution, as I have
yet to find an adequate one size fits all method of creating PDF's from
Access. Virtually every customer I have is using different PDF creators, or
different versions of the same software, all of which seem to have
idiosyncrasies. I am forever having to tweak this and change that to make
things work. What's worse it makes me look as if I do not know what the
heck I am doing. This is something that OUGHT TO BE EASY to implement, but
trips me up almost all of the time.

This solution looks VERY promising and I am willing to buy the DynaPDF tool
if I can get the glue you used to hook Access Reports and Snapshots up to
it. I have downloaded their Eval an looked at the documentation and VB
examples. I find that is architected similar to but with much more control
and complexity than a Package I already own (DynammicPDF). The fact that it
is an Xcopy install makes it even more interesting.

I am having problems with the Access2K version that I downloaded from your
site. It complains:

fCreateSnap.fParseSnap:-2147220958
Sorry...Page size not supported
Please select a different Report to Export

No matter what I throw (Report or existing Snapshot) at it I get this same
message. I have tried using it directly from the example you provided in
the mde, and as an external library to one of my projects. I thought
perhaps a problem with A4 vs. Letter Page setups, but no joy.

So I guess the questions are:
1) Is there a known problem with the Access 2K version
2) Are you willing to share the Access to DynaPDF source WITHOUT the
DynaPDF License Key
3) Would you be willing to give us a hint of how the glue works so I
could attempt to come up with an independent solution.

From my short observation it appears that you may be parsing a snapshot of
the report, and translating the objects that you find there, into calls to
the DynaPDF library. Can you point me to any good documentation of how to
parse the snapshot format? I would most appreciative of any help you can
provide.

Thanks

 

[Stephen Lebans]

While it's Beta release, I have not heard of any problems with the A2K
release.

There is an issue which I have publically mentioned a few times and is the
same issue you are experiencing. It happens with unbound reports and reports
that are not formatted correctly to fit on the selected output page(margins
too large, paper too small etc.). I won't repeat here the lengthy Structured
Storage issue that is the cause of the above issues. The solution is to use
a third party ActiveX control which I simply do not want to do because of
distribution/registration issues. I am working on new logic to parse the
Structured Storage file which I will incorporate into the next release
shortly.
But you should not be seeing this bug if you are using your own reports or
have converted my reports(Letter size) to your own page size. Can you send
me one of your reports that fails?

I cannot make the source of the MDE available because:

1) It contains the License Key for the DynaPDF library.
2) It will shortly contains intellectual property that I signed a non
disclosure agreement to gain access to.

So at this point, I will not be able to release my source code.

Ron why do you need to come up with an independant solution?
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Silvester]

Stephen,

Did you get a chance to read my message at the start of this thread. I'm
looking to find the best way to deploy and distribute without running into
broken ref issues with the mde.

What do you recommend ?

TIA

 

[Ron Weiner]

Stephen

I have (after much experimentation) found the cause of my problem. It
appears to be the Printer Driver I was using (HP LaserJet 1200 Series PCL 6)
was the culprit. I changed the print driver to a plain vanilla HP LaserJet
4 and **poof** no more errors. PDF's are created as per the documentation
in your MDE.

However, as long as this is beta I wonder if you might consider making a
change or two.

1) It would be nice if there was a way to keep Acrobat from opening each
time a pdf was created.
2) It would be nice if we could pass both the report Filter and report
Where condition as parameters
3) It would be nice if we could specify the name and location of the
temp .snp files (I have a bunch temp files hanging around from all of the
failed attempts I have made.
4) Could you distribute the MDE with NO Forms or Reports in it, in an
effort to keep it as small as possible.

Of these three the first one is the most important to me. Most of the time
I am making a PDF just to Email it somewhere. There would be no need to
open the PDF viewer, the sender would already know what was in the report as
they will have likely previewed it before sending the email.

If you are looking for testing help as you move your Beta along please feel
free to contact me. I would be happy to help.

As for an independent solution, I have always liked the idea of having full
control over how my app is going to work. I would be more than happy to use
your solution as you have it (abet the change for opening Acrobat after PDF
creation), but I am concerned about adding a reference to my mde's. Is
there any way I can use something similar to late binding with your mde. I
am sure that I will get a support call some day where the client has done
something that caused Access to forget that it had a reference and stop
making PDF's. If I were able to use some sort of late binding technique
that support call could take a lot less time and effort.

Anyway thanks for your time and all the help you have provided to me in the
past. It is and has always been most appreciated.

 

[Ron Weiner]

Stephen / Silvester

Here is a test function that seems to work well enough. BEWARE this is just
a test of automating Stephens MDE without having to set a reference to it. I
have all of the file locations, and report names hard coded.

I think calling Stephens function this way opposed to setting a reference,
makes the calling application less prone to fail, and requires much less end
user intervention to get the app working should a failure occur.

In the real application I plan to have the full filespec to Stephens MDE in
a table where I can do a simple Dlookup, and make changes in a heartbeat.
The Report Name, Temp SnapShot, and Output PDF filespecs will be passed
from the caller. The function returns the filespec of the PDF on success
and an empty string should an error occur.

Public Function Rpt2PDF() As String

Dim o As Access.Application

On Error GoTo Err_Rpt2PDF
Set o = New Access.Application
o.OpenCurrentDatabase "C:\PPI\ReportToPDF.mde"
DoCmd.OutputTo acOutputReport, "rptProject", acFormatSNP,
"C:\PPI\ddd.snp"
o.Run "ConvertReportToPDF", "", "C:\PPI\ddd.snp", "c:\Z.pdf"
Kill "C:\PPI\ddd.snp"
Rpt2PDF = "c:\Z.pdf"
Err_Rpt2PDFOut:
If o.CurrentObjectType > 0 Then
' Appears to always be -1 when OpenCurrentDatabase fails
o.CloseCurrentDatabase
End If
Set o = Nothing
Exit Function
Err_Rpt2PDF:
Rpt2PDF = ""
Debug.Print Err.Number, Err.Description
Resume Err_Rpt2PDFOut
End Function

If only I could keep Acrobat from opening with the new PDF file, I would be
in "Super Fat City", where all the girls are pretty.

 

[Stephen Lebans]

Hi Ron ,
that looks like a good option. With your permission, I'll add your
method/logic/comments to the next release and the ReportToPDF web page.

I've had my head stuck in Structured Storageup tech papers the last few
days. I finally found some very old code I could work with. I created a
simple Windows System DLL last night and the modified code seems to work in
prelim testing.

I added the Boolean param to signal; whether to open the created file in the
registered PDF viewer or not. I also added another param but cannot remember
what it was right now...getting older.

Will be a new release shortly.

I am considering rediscovering my way out of date C++ skills to create a
customized version of the DynaPDF library. This way I could include all of
my current Access code/logic directly within the DynaPDF DLL. I would then
use the same logic you posted, create the Snapshot and then pass the
Snapshot to the ConvertReportToPDF function. No MDE required, no mess, no
fuss!


--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Ron Weiner]

Stephen

I would consider it an honor to have my name linked to your work. By all
means if you think it helpful, please go right ahead. In the mean time I
eagerly await the release of your next version. Please do give me a heads
up when you release the new code.

I didn't realize you had the DynaPDF source. If you can get all of the
Snapshot - to - PDF logic into a single DLL then you are really cookin'.
You will have commercially viable product that could bring in revenue until
MS releases the next version of Access. Based on what I see in the press, I
expect in the next release we will get at least some kind of direct report
to PDF capability.

However not everyone upgrades. I no longer have any Access 97 customers,
but I continue to do all of my Access development in A2K so I can reach the
largest possible audience with a single code base. Should the next version
of Access be binary compatible with A2k I would be VERY hesitant to change
my development platform.

 

[Stephen Lebans]

Ron I am not doing this for any monetary gain. As an MVP I knew a while ago
that PDF support was going to be available in Office 12. Even with a release
date of next year, it will be several years before the majority of users
upgrade. I just feel that PDF support should be included in Access.

The structured storage code appears to be working well and I haven't GPF'd
yet but more testing is required. It's a long weekend up here coming up and
I plan to spend the majority of it testing and debugging.
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Stephen Lebans]

Posted new version tonight.
http://www.lebans.com/reporttopdf.htm
Make sure you download the archive that matches your version of Access.
Please view the form named README before launching the converter.

New Release: Nov 08/2005

A2003ReportToPDF is an Access 2003 database containing a function to convert
Reports and Snapshot files to PDF documents. No PDF Printer driver is
required.

A2002ReportToPDF A2002 Version

A2000ReportToPDF A2000 Version



A97 version coming soon.

Version 1.8

Added Boolean parameter to signal whether the registered PDF application
should be launched to view newly created PDF document. Add StrStorage.DLL to
fix issue with parsing of Snapshot file. This will also fix non USA issue(
Error Paper Size is not supported). The A2K MDB showed corruption upon
conversion from the original A2003 version. I imported everything into a
blank MDB and the corruption has disappeared. Please let me know if it
resurfaces.

Version 1.7 First release


--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Silvester]

Thanks Stephen & Ron,

Stephen, now all we're eagerly awaiting is the ability to pass on the where
clause filters to reports being converted to pdf. And then we'll all be with
you in Super Fat City, Ron...

 

[Silvester]

Ok, Here's my 2c worth until Stephen comes up with a where clause filter...

Here's how I pass on filters to reports being batch converted to pdf in a
loop. Works great for me.

stLinkCriteria = "[Some Field]=" & "'" & msomevalue & "'"
'save the report filter
Call SetReportFilter(stDocName, stLinkCriteria)
Call convertreporttopdf(stDocName, , strPDFOutputFilePath & ".pdf", False,
False)
'reset report filter to nothing
Call SetReportFilter(stDocName, "")

----------------------------------------------------------
' This code credit goes to Crystal
http://www.utteraccess.com/forums/u...hat=showflat&page=&view=&sb=5&o=&fpart=1&vc=1

Sub Report_SetReportFilter(pReportName, pFilter)
Dim rpt As Report
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)
rpt.Filter = pFilter
rpt.FilterOn = true
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName
Set rpt = Nothing
End Sub
-------------------------------------------------------

 

[Silvester]

Ron,
My quick n dirty way of killing the reader when it opened... This, of
course, was before Stephen gave us the switch off for the Reader Preview...

invoke with:
Call gfncCloseApp("Adobe Reader")

'module code pointed out by Peter Shroeder
'--------------------------------------------------
Option Compare Database
Option Explicit

Private Declare Function apiGetDesktopWindow Lib "user32" Alias
"GetDesktopWindow" () As Long
Private Declare Function apiGetWindow Lib "user32" Alias "GetWindow" (ByVal
hwnd As Long, ByVal wCmd As Long) As Long
Private Declare Function apiGetWindowText Lib "user32" Alias
"GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal aint
As Long) As Long
Private Declare Function apiGetWindowThreadProcessId Lib "user32" Alias
"GetWindowThreadProcessId" (ByVal hwnd As Long, lpdwProcessID As Long) As
Long
Private Declare Function apiIsWindow Lib "user32" Alias "IsWindow" (ByVal
hwnd As Long) As Long
Private Declare Function apiPostMessage Lib "user32" Alias "PostMessageA"
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lparam As
Any) As Long
Private Declare Function apiWaitForSingleObject Lib "kernel32" Alias
"WaitForSingleObject" (ByVal hHandle As Long, ByVal dwMilliseconds As Long)
As Long

Private Const GW_CHILD = 5
Private Const GW_HWNDNEXT = 2
Private Const INFINITE = &HFFFFFFFF
Private Const WM_CLOSE = &H10

Function gfncCloseApp(pstrWindowName As String) As Boolean

'Error-handler inserted
'

On Error GoTo gfncCloseApp_Error


Dim lngClosedCount As Long, lngFoundCount As Long, lngHWnd As Long, lngPID
As Long
Dim strWindowName As String

If Len(pstrWindowName) > 0 Then
lngHWnd = apiGetWindow(apiGetDesktopWindow, GW_CHILD)
Do While lngHWnd <> 0
strWindowName = Space$(255)
Call apiGetWindowText(lngHWnd, strWindowName, 255)
If Left$(strWindowName, Len(pstrWindowName)) = pstrWindowName Then
lngFoundCount = lngFoundCount + 1
Call apiPostMessage(lngHWnd, WM_CLOSE, 0, ByVal 0&)
Call apiGetWindowThreadProcessId(lngHWnd, lngPID)
Call apiWaitForSingleObject(lngPID, INFINITE)
If apiIsWindow(lngHWnd) <> 0 Then lngClosedCount = lngClosedCount +
1
End If
lngHWnd = apiGetWindow(lngHWnd, GW_HWNDNEXT)
Loop
End If
gfncCloseApp = (lngClosedCount = lngFoundCount) And (lngClosedCount > 0)


gfncCloseApp_Exit:
Exit Function

gfncCloseApp_Error:

Select Case Err.Number

Case Else
MsgBox "Error - " & Err.Number & vbCrLf & vbCrLf & Error$, vbExclamation,
GetAppName()

End Select

Resume gfncCloseApp_Exit

End Function

 

[Stephen Lebans]

I cannot see how I could integrate this into my solution. The application of
filters or criteria for the underlying recordset must remain with the
individual user/developer. For a moment, forget that you are exporting to
PDF. How would you export to Snapshot or RTF format if you needed to apply a
filter of supply specific criteria to filter the underlying recordset?
ReportToPDF exports the report to Snapshot format and then parses the
Snapshot file for the individual Metafile records. These are then converted
to their PDF counterparts.
So whatever you would normally do to export your report to a Snapshot file,
with whatever filters you need, is how you must approach exporting to PDF.

I will post all of your suggestings for handling this issue on the
ReportToPDF web page.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Stephen Lebans]

Have you tested this extensively Sylvester?

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Stephen Lebans]

Well Sylvester, you are now me. This is exactly the kind of solution I would
have supplied in the past. I love it!<grin>

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Ron Weiner]

Stephen

Got the new version, refactored the Access automation code, and it works
great with the following printer drivers:
HP 4
HP 5
HP 4050
HP Color LaserJet 5/5M

It does not behave well with the following printer drivers:
hp deskjet 895c series
HP LaserJet 1200 Series PCL 6
hp photosmart 1115 series

On the printers where it does not work well, it no longer dies with the
"page size not supported" error, but it does create PDF's with *VERY* weird
page sizes. Doing some quick testing I got the following results.

hp deskjet 895c series page size 0.38 x 0.39 in
HP LaserJet 1200 Series PCL 6 page size 0.04 x 0.04 in
hp photosmart 1115 series page size 0.04 x 0.04 in

Would be a tremendous way to save paper, but it's really hard to read. At
1200 DPI the 0.04 x 0.04 page can only hold 48 dots in each direction.
All of my testing was done on a simple one page report, text only, that was
set with 1" margins all around and standard US Letter size paper. Perhaps
with these clues you will get a eureka moment, and solve the mystery.

The best part is I conducted a quick test at the client site and it works
great there also. So for now I am in "Semi Fat City". All that is left for
me to do is to replace all of the lines on the reports with boxes, and have
the reports get their "WHERE" condition from a hidden field on the form that
calls them. I have made the decision to deploy this version and upgrade
when you have DLL only solution.

Buy the way, my customer is running Access 2002 and I tested with your
Access 2K MDE without problem. I deploy all of my Access projects as A2K
MDE's (except for my Runtime guys) with no problems. Is there anything that
I should be on the lookout for from your experience?

I can't even begin to thank you enough for your help with the surprisingly
difficult (for me at least) task of programmatically generating PDF 6files
from Access. I have no doubt that your method is destined to become yet
another "Best Practices" for the Access Developer community.

I am in your debt. I can't imagine what it might be, but please do not
hesitate to call on me should you need another pair of eyes or hands for
testing or whatever.

 

[Stephen Lebans]

Dieter sent me a Snapshot so I was able to pinpoint the problem. I do not
understand why certain printer drivers do not fill in the printer's DevMode
PageWidth and PageHeight values. They do fill in the PaperSize member so I
can work with this value to generate the PageWidth and PageHeight values.
It's just a drag having to type in a very long Select Case statement. I'm
half way done as we speak.

There is no problem using a A2K MDE with later versions as I am not doing
anything that would cause version incompatabilities. In fact, I am getting
tired of posting three versions of the MDE everytime there is an update.
Until the solution is more stable, I will only post the A2K MDE for now.

I am hoping to have a resolution to the Line tool issue shortly. Then it
will be a complete solution requiring no modification of existing reports.

Next will come the fun stuff. The DynaPDF library is unbelieveable in the
functionality it contains. Imagine sending out a PDF generated from your
report, having the user mnodify or fill in the fields directly in the PDF
and then sending it back to you to be incorporated back into your MDB. Gonna
be fun!

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Silvester]

Now Yoda, there's the best compliment young Skywalker has had in a long time

 

[Silvester]

Master Yoda, if you're posting *my* suggestions onto *YOUR* website, then
Silvester's real name is Roland Karkada. I'm no MVP, so hey, this is a big
honour. Now where is my speech ??