Link to home
Create AccountLog in
Avatar of rowfei
rowfei

asked on

Save report to PDF in access 2003

What codes that I can use to save access report to a PDF and then save the pdf to local disk. Then auto open a email with pdf as attachment. The email send to and topic are auto pops.

Thanks
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

You can use Stephen Lebans to save to PDF:

www.lebans.com/reporttopdf.htm

How would you send the email? Many recommend using CDO; I use the control from Codestone (www.codestone.co.uk) but either will work. CDO is "free", as it's already installed.

http://www.granite.ab.ca/access/email/outlook.htm

See the link above for info on sending email.
Avatar of rowfei
rowfei

ASKER

Thanks,
I have Adobe Acrobat 8.0 Professional intalled in my computer. I have use the following codes to convert report to PDF:

Dim strDefaultPrinter As String

'Get the default printer being used
strDefaultPrinter = Application.Printer.DeviceName

' Switch the Default Printer to print to Adobe
Set Application.Printer = Application.Printers("Adobe PDF")

'Create the PDF File / Print to PDF
DoCmd.OpenReport "R_EmployeeByDept"

'Reset the printer to the original default printer
Set Application.Printer = Application.Printers(strDefaultPrinter)

Now I would like to have the following improvement:

1) Auto save the PDF on a network drive
2) Rename the PDF title as same as the source data modification date & time
3) Open outlook email and attach the pdf with default to, topic and body

Thank you so much if anyone can provide some codes that give to me what I needs.

Regards,
rowfei,

Just FYI:

You really have 3 separate questions here
See here:
https://www.experts-exchange.com/help.jsp#hi23

I am sure that you will get the answer(s) you are looking for.
But sometimes multiple questions cause problems for the reasons listed in the link.
;-)

Again, just FYI
JeffCoachman
This code shows how to use Adobe to save your report to a named location:

http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=35321&lngWId=1

This link, which I included in my first post:

http://www.granite.ab.ca/access/email/outlook.htm

will provide you all you need to automate Outlook and attach your PDF file ...
Avatar of rowfei

ASKER

Thanks, But I still can't find the code to to include with the code above that will
automatically save the file in a location on our network. Now after running the code above, I get a "save as" dialog box that prompts me to select the location to which to save the file.

The RunReportAsPDF is the function that would do this ... it would be called like this:

RunReportAsPDF "rptSales", "//SomeMachine/Folder","Sales Report 2008.pdf"

Is this what you're doing?
Public Function RunReportAsPDF(rptName As String, sPDFPath As String, sPDFName As String)
    '---------------------------------
    'rptName = Microsoft Access report name 
    '     you
    'want to create pdf from
    'sPDFPath = the directory path where you
    '     want
    'to create the pdf file (ex. - "c:\data\
    '     ")
    'sPDFName = the name of the pdf file you
    '     are
    'wanting to create (ex. - "file001.pdf")
    '     
    '---------------------------------
    Dim sMyDefPrinter As String
    On Error Goto Err_RunReport
    'Save current default printer
    sMyDefPrinter = bGetRegValue(HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device")
    ' Set default printer to PDF Writer
    bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "Acrobat PDFWriter"
    'Setting value for PDFFileName in the re
    '     gistry stops file dialog box from appear
    '     ing
    bSetRegValue HKEY_CURRENT_USER, "Software\Adobe\Acrobat PDFWriter", "PDFFileName", sPDFPath + sPDFName
    'Run the report
 
 
    DoCmd.OpenReport rptName, acViewNormal
        Exit_RunReport:
        ' Restore default printer
        bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device", sMyDefPrinter
        Exit Function
        Err_RunReport:
        MsgBox Err.Description
        Resume Exit_RunReport
    End Function

Open in new window

Avatar of rowfei

ASKER

Thanks, But I got the "Complie error: Sub or Function not defined" on "sMyDefPrinter = bGetRegValue(HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device")

It highligh on bGetRegValue.

Thanks
That was a copy/paste from the earlier link I provided .... did you copy/paste this code into your project, including the General Declarations section? If so, you should already have that function in your project.
Avatar of rowfei

ASKER

Here is what I did, please correct me if I am wrong.

1) Copy all the codes in your earlier link "http://www.planet-sourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=35321&lngWId=1" to a new Modules and name is ExportPDF.

2) Put the following codes in the click even of "Export" button of form:
RunReportAsPDF "rptSales", "//SomeMachine/Folder","Sales Report 2008.pdf"

As I click the "Export" button and a message pops up "Invalid procedure call or argument."

Please advise.

Thanks

Sounds correct, expect you must change the RunReportAsPDF arguments to match your own (i.e. the correct ReportName, Path, and FileName).
Avatar of rowfei

ASKER

I did, but I still get Invalid procedure call or argument as I click "Export" button.
Okay ... then what, exactly, were the arguments you used? It's a lot easier when we can actually see the stuff you're passing into the procedures.

Also, set a breakpoint in the code at the beginning of RunReportAsPDF, then step through the code to determine exactly which line is causing your problem.
Avatar of rowfei

ASKER

Sorry, I really can't tell where is wrong.

Below is the codes that I put in the ExportPDF Modules:

Option Compare Database

Declare Function RegEnumValue Lib "advapi32.dll" Alias "RegEnumValueA" (ByVal hKey As Long, ByVal dwIndex As Long, ByVal lpValueName As String, lpcbValueName As Long, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long


Declare Function RegOpenKeyEx Lib "advapi32" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long


Declare Function RegSetValueEx Lib "advapi32" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal szData As String, ByVal cbData As Long) As Long


Declare Function RegCloseKey Lib "advapi32" (ByVal hKey As Long) As Long


Declare Function RegCreateKeyEx Lib "advapi32" Alias "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions As Long, ByVal samDesired As Long, lpSecurityAttributes As SECURITY_ATTRIBUTES, phkResult As Long, lpdwDisposition As Long) As Long


    #If Win32 Then
        Public Const HKEY_CLASSES_ROOT = &H80000000
        Public Const HKEY_CURRENT_USER = &H80000001
        Public Const HKEY_LOCAL_MACHINE = &H80000002
        Public Const HKEY_USERS = &H80000003
        Public Const KEY_ALL_ACCESS = &H3F
        Public Const REG_OPTION_NON_VOLATILE = 0&
        Public Const REG_CREATED_NEW_KEY = &H1
        Public Const REG_OPENED_EXISTING_KEY = &H2
        Public Const ERROR_SUCCESS = 0&
        Public Const REG_SZ = (1)
    #End If


Type SECURITY_ATTRIBUTES
    nLength As Long
    lpSecurityDescriptor As Long
    bInheritHandle As Boolean
    End Type
 



'**************************************
' Name: Create PDF from MS Access Report
'
' Description:On a machine where the Ado
'     be PDFWriter is installed, the current p
'     rinter is swapped out with the PDFWriter
'     and the PDF file is created. The origina
'     l printer is then restored.
' By: Todd Benson
'
' Inputs:rptName = Microsoft Access repo
'     rt name you want to create pdf from. sPD
'     FPath = the directory path where you wan
'     t to create the pdf file (ex. - "c:\data
'     \"). sPDFName = the name of the pdf file
'     you are wanting to create (ex. - "file00
'     1.pdf").
'
' Assumes:This code is easily modified t
'     o be used in other programs
'
' Side Effects:Please use the most recen
'     t installs of Adobe Exchange or PDFWrite
'     r to ensure proper functionality.
'
'This code is copyrighted and has' limited warranties.Please see http://w
'     ww.Planet-Source-Code.com/vb/scripts/Sho
'     wCode.asp?txtCodeId=35321&lngWId=1'for details.'**************************************



Public Function bGetRegValue(ByVal hKey As Long, ByVal sKey As String, ByVal sSubKey As String) As String
    Dim lResult As Long
    Dim phkResult As Long
    Dim dWReserved As Long
    Dim szBuffer As String
    Dim lBuffSize As Long
    Dim szBuffer2 As String
    Dim lBuffSize2 As Long
    Dim lIndex As Long
    Dim lType As Long
    Dim sCompKey As String
    Dim bFound As Boolean
    lIndex = 0
    lResult = RegOpenKeyEx(hKey, sKey, 0, 1, phkResult)


    Do While lResult = ERROR_SUCCESS And Not (bFound)
        szBuffer = Space(255)
        lBuffSize = Len(szBuffer)
        szBuffer2 = Space(255)
        lBuffSize2 = Len(szBuffer2)
        lResult = RegEnumValue(phkResult, lIndex, szBuffer, lBuffSize, dWReserved, lType, szBuffer2, lBuffSize2)


        If (lResult = ERROR_SUCCESS) Then
            sCompKey = Left(szBuffer, lBuffSize)


            If (sCompKey = sSubKey) Then
                bGetRegValue = Left(szBuffer2, lBuffSize2 - 1)
                RegCloseKey phkResult
                Exit Function
            End If
        End If
        lIndex = lIndex + 1
    Loop
    RegCloseKey phkResult
End Function


Public Function bSetRegValue(ByVal hKey As Long, ByVal lpszSubKey As String, ByVal sSetValue As String, ByVal sValue As String) As Boolean
    On Error Resume Next
    Dim phkResult As Long
    Dim lResult As Long
    Dim SA As SECURITY_ATTRIBUTES
    Dim lCreate As Long
    RegCreateKeyEx hKey, lpszSubKey, 0, "", REG_OPTION_NON_VOLATILE, KEY_ALL_ACCESS, SA, phkResult, lCreate
    lResult = RegSetValueEx(phkResult, sSetValue, 0, REG_SZ, sValue, CLng(Len(sValue) + 1))
    RegCloseKey phkResult
    bSetRegValue = (lResult = ERROR_SUCCESS)
End Function


Public Function RunReportAsPDF(rptName As String, sPDFPath As String, sPDFName As String)
    '---------------------------------
    'rptName = Microsoft Access report name
    '     you
    'want to create pdf from
    'sPDFPath = the directory path where you
    '     want
    'to create the pdf file (ex. - "c:\data\
    '     ")
    'sPDFName = the name of the pdf file you
    '     are
    'wanting to create (ex. - "file001.pdf")
    '
    '---------------------------------
    Dim sMyDefPrinter As String
    On Error GoTo Err_RunReport
    'Save current default printer
    sMyDefPrinter = bGetRegValue(HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device")
    ' Set default printer to PDF Writer
    bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Windows", "Device", "Acrobat PDFWriter"
    'Setting value for PDFFileName in the re
    '     gistry stops file dialog box from appear
    '     ing
    bSetRegValue HKEY_CURRENT_USER, "Software\Adobe\Acrobat PDFWriter", "PDFFileName", sPDFPath + sPDFName
    'Run the report


    DoCmd.OpenReport rptName, acViewNormal
Exit_RunReport:
        ' Restore default printer
        bSetRegValue HKEY_CURRENT_USER, "Software\Microsoft\WIndows NT\CurrentVersion\Windows", "Device", sMyDefPrinter
        Exit Function
Err_RunReport:
        MsgBox Err.Description
        Resume Exit_RunReport
    End Function
 
 
Below is codes that I put under Export button Click event;

Private Sub cmdExport_Click()

RunReportAsPDF "rpt_List", "C:\Report\", "HighList.pdf"

       
End Sub

Okay ... do you actually have a C:\Report directory? If not, create it and see what happens.

If you do, then can you open and run rpt_List normally (i.e. click on the report in the db window)?
Avatar of rowfei

ASKER

Yes, I double check the C:\Report directory. I don't have any problem by openning rpt_list normally.

Now I get 'You must instal a printer before you print" message after I click the Export button.

I double check the printer setup and I do have Adobe PDF printer icon under Prnters and Faxes. By the way, I have Windows XP.

Thanks
Do you actually have a printer named "Acrobat PDFwriter" on your machine? Open your report manually, then click file - Print and see if you have this printer listed.
Avatar of rowfei

ASKER

I open my report manually and then click file - Print and I see "Adobe PDF". I have Adobe Acrobat 8.0 Pro installed in my computer
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of rowfei

ASKER

Thanks