johnberniejones
asked on
Output report to PDF and send to email
I have been reading looking through some of the solutions for output of a report to PDF. I have used the PDF995 program. I have entered the following code, however when I run this function as the On Click event for a button on the form.
"The expression On Click you entered as the event property setting produced the following error: Only comments may appear after End Sub, End Function, or End Property.
Option Compare Database
Option Explicit
'Read INI settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA"
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 _
"WritePrivateProfileString
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:\pdf995\res\pdf995.ini"
syncfile = "c:\documents and settings\all users\application data\pdf995\res\pdfsync.in
' build the output file name from the path parameter and the report name
If Mid(destpath, Len(destpath), 1) <> "\" Then destpath = destpath & "\"
outputfile = destpath & reportname & ".pdf"
' 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(
x = WritePrivateProfileString(
' 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("PDF9
'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(
x = WritePrivateProfileString(
x = WritePrivateProfileString(
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(sS
sDefault$, sRetBuf$, iLenBuf%, sFilename)
ReadINIfile = Left$(sRetBuf$, x)
End Function
"The expression On Click you entered as the event property setting produced the following error: Only comments may appear after End Sub, End Function, or End Property.
Option Compare Database
Option Explicit
'Read INI settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA"
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 _
"WritePrivateProfileString
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:\pdf995\res\pdf995.ini"
syncfile = "c:\documents and settings\all users\application data\pdf995\res\pdfsync.in
' build the output file name from the path parameter and the report name
If Mid(destpath, Len(destpath), 1) <> "\" Then destpath = destpath & "\"
outputfile = destpath & reportname & ".pdf"
' 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(
x = WritePrivateProfileString(
' 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("PDF9
'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(
x = WritePrivateProfileString(
x = WritePrivateProfileString(
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(sS
sDefault$, sRetBuf$, iLenBuf%, sFilename)
ReadINIfile = Left$(sRetBuf$, x)
End Function
Hmm - it looks OK.
At the risk of sounding like a broken record - have you done the standard?
Compact/Repair
Decompile
Import to new mdb
At the risk of sounding like a broken record - have you done the standard?
Compact/Repair
Decompile
Import to new mdb
ASKER
Yeah tried that No Joy.
Why would it be giving me the response when there is no End Sub prior to the following statement?
'Read INI settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA"
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long
Why would it be giving me the response when there is no End Sub prior to the following statement?
'Read INI settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA"
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long
This is in a standard module?
Looking at your post - there's nothing above this only the usual
Option Compare Database
Option Explicit
The function isn't declared anywhere else?
And you've decompiled and imported to a new mdb? (Not just compacted/repaired)
Looking at your post - there's nothing above this only the usual
Option Compare Database
Option Explicit
The function isn't declared anywhere else?
And you've decompiled and imported to a new mdb? (Not just compacted/repaired)
Do you have these declared in the "middle" of a module? All API declares must be in the General Declarations section of a module (the very top, before the first Sub or Function call).
Yeah I wondered that too - but looking at the post it looks like it's right under Option Explicit. :-(
ASKER
I've used this in the pdfwrite On click event
Private Sub pdfwrite_Click()
I have imported the database into a new mdb. I don't know how to compile? Sorry
Private Sub pdfwrite_Click()
I have imported the database into a new mdb. I don't know how to compile? Sorry
In a shortcut, command line or from the Run dialog prompt
"Path To Access Exe" "Path To mdb" /decompile
e.g.
"C:\Program Files\Microsoft Office\Office 11\MSAccess.exe" "C:\Data\myStuff.mdb" /decompile
(depending on your version of Office)
"Path To Access Exe" "Path To mdb" /decompile
e.g.
"C:\Program Files\Microsoft Office\Office 11\MSAccess.exe" "C:\Data\myStuff.mdb" /decompile
(depending on your version of Office)
You can't paste the code block above into an Event (which is what the _Click is) ... you should, instead, build a new Standard Module and move all of the code you indicated to that module. After doing this, you would change your click event to this:
Private Sub pdfWrite_Click()
pdfwrite "YourReportName", "Path To Where You Want To Save The PDF File", "Optional Criteria For The Report, if Desired"strcriteria As String)
End Sub
Private Sub pdfWrite_Click()
pdfwrite "YourReportName", "Path To Where You Want To Save The PDF File", "Optional Criteria For The Report, if Desired"strcriteria As String)
End Sub
I believe you'd rather Compile your project (although Decompile is a good way to rid your db of corruption). To Compile your project:
1) Open the VB Edit window (Ctrl + G will get you there)
2) Click Debug - Compile
The operation will show you any errors that will not allow your code to compile ... you will have to fix these errors before you can successfully compile your code. Note that Access will NOT alert you of a successful compile ... it will simply grey out the Compile command, so if you click Compile and nothing seems to happen, your code has compiled correctly and you're good to go!
1) Open the VB Edit window (Ctrl + G will get you there)
2) Click Debug - Compile
The operation will show you any errors that will not allow your code to compile ... you will have to fix these errors before you can successfully compile your code. Note that Access will NOT alert you of a successful compile ... it will simply grey out the Compile command, so if you click Compile and nothing seems to happen, your code has compiled correctly and you're good to go!
Oh wow.
Sorry - I'd not even considered that.
Might it be the actual Option Compare Database line that's causing that error - because it's been pasted into the procedure of a control event?
I asked earlier on "This is in a standard module?"
But you possibly didn't know what I meant by that?
Wasn't thinking "outside the box".
:-)
(Or even outside my bed seemingly)
So where are you now?
Sorry - I'd not even considered that.
Might it be the actual Option Compare Database line that's causing that error - because it's been pasted into the procedure of a control event?
I asked earlier on "This is in a standard module?"
But you possibly didn't know what I meant by that?
Wasn't thinking "outside the box".
:-)
(Or even outside my bed seemingly)
So where are you now?
ASKER
OK.
Tried to decomplie and it is sticking on the line
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA"
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long
The Module is based on my Form_frmConsult. I have now placed the Function at the top of the Module so Option Compare Databae is at the top.
Where to from here
Tried to decomplie and it is sticking on the line
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA"
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long
The Module is based on my Form_frmConsult. I have now placed the Function at the top of the Module so Option Compare Databae is at the top.
Where to from here
IF this is in the Form's module, you'll have to declare the functions as PRivate:
Private Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA"
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long
You'll also have to move the other items around, as I described earlier ... you can't declare Subs inside of Subs:
Private Sub MySub()
<code here>
Sub SomeOtherSub()
<code here>
End Sub '/<< end of SomeOtherSub
End Sub '/<< end of MySub
Private Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA"
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long
You'll also have to move the other items around, as I described earlier ... you can't declare Subs inside of Subs:
Private Sub MySub()
<code here>
Sub SomeOtherSub()
<code here>
End Sub '/<< end of SomeOtherSub
End Sub '/<< end of MySub
Just dumping the whole thing into a standard module and then calling it as Scott mentioned earlier.
Private Sub pdfWrite_Click()
pdfwrite "YourReportName", "Path To Where You Want To Save The PDF File", "Optional Criteria For The Report, if Desired"strcriteria As String)
End Sub
Makes your form's code much more clean.
Private Sub pdfWrite_Click()
pdfwrite "YourReportName", "Path To Where You Want To Save The PDF File", "Optional Criteria For The Report, if Desired"strcriteria As String)
End Sub
Makes your form's code much more clean.
ASKER
How Do I now attach this to a macro/button to allow printing of the report?
To be clear, what have you done so far: Moved the code to a Standard Module, or left it in the Form's MOdule?
ASKER
Ok... I think I'm getting It. I've moved it to a new Module which I've named pdfwrite. I'm now entering the code Private Sub pdfWrite_Click()
pdfwrite "DoctorsReport", "C:\
End Sub
It is returning the Complie error: Invalid use of property
pdfwrite "DoctorsReport", "C:\
End Sub
It is returning the Complie error: Invalid use of property
You mustn't call your module pdfwrite too.
Call it baspdfwrite or whatever.
Then you'll need
pdfWrite_Click()
pdfwrite "DoctorsReport", "C:\"
End Sub
Call it baspdfwrite or whatever.
Then you'll need
pdfWrite_Click()
pdfwrite "DoctorsReport", "C:\"
End Sub
I believe LPurvis meant:
pdfWrite_Click()
baspdfwrite "DoctorsReport", "C:\"
End Sub
What version of Access are you using?
pdfWrite_Click()
baspdfwrite "DoctorsReport", "C:\"
End Sub
What version of Access are you using?
Oh - I meant rename your module baspdfwrite.
Keep the function name as it is.
So it would be much as it was
Private Sub pdfWrite_Click()
pdfwrite "DoctorsReport", "C:\"
End Sub
Keep the function name as it is.
So it would be much as it was
Private Sub pdfWrite_Click()
pdfwrite "DoctorsReport", "C:\"
End Sub
ASKER
I'm using MS Access 2003. I was able to get the baspdfwrite working, however I don't know how to attach the module to a button on a form?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
'Read INI settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA"
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long