Need some help with VBA modules and Access 2003 macros

Posted on 2014-02-18
Last Modified: 2014-02-19
I have an access database that automatically writes a snp report called "WeeklyNM" to a drive folder locally using the OutputTo action of the macro. All was well until the powers that be decided they need that report in PDF format. So I found on the internet some code that works with a PDF printer called PDF995 to do this. I pasted the code below into a new module called "modPDFWrite" The instructions on the site I found the code said call the module using the line pdfwrite "ReportName", "c:\folder name". My question is how do I use this with a macro to automate it? I found an OpenModule action but I don't know if that's right or not.


Here's the code:

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)

Public Function FileFolderExists(strFullPath As String) As Boolean
'Author       : Ken Puls (
'Macro Purpose: Check if a folder exists
    On Error GoTo EarlyExit
    If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
    On Error GoTo 0
End Function

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 (x86)\pdf995\res\pdf995.ini"
syncfile = "c:\programdata\pdf995\pdfsync.ini"

    If FileFolderExists(destpath) Then
        'MsgBox "Folder exists!"
        MsgBox "Folder does not exist! Exiting...."
        GoTo Cleanup
    End If

' 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("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

' restore the original default printer and the PDF995.ini settings
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

Open in new window

Question by:gwbmcse
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 39868949
to call the codes from a macro

first, you have to create a function in a module, you can place the function in the same module  "modPDFWrite"

Function CreatePDFReport()

' call the sub pdfwrite here

  pdfwrite "ReportName", "c:\folder name"

End Function

next, in a macro design view place a line


' in the Function name place the name of the function

Function Name   CreatePDFReport()


Author Closing Comment

ID: 39870532
Bingo...hit the nail on the head. Thanks.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

752 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