Save Report As PDF

[all7holly]

Hello,

How do I save a report to PDF with VBA code?
Name that File with a project number and the Report Name
Then send it to a certain File Path?

All with the click of a button. Any ideas?

Regards,
Holly

 

[vbaInet]

You may search the forum (using the search utility) for OutpuTo for this topic has been discussed many times.

 

[smig]

what version of Access ?

 

[all7holly]

Access 2007.

 

[vbaInet]

Have you searched the forum for OutputTo? Sorry, I missed out the 't' in my last post.

 

[all7holly]

I can't seem to get this to work.

Private Sub Command164_Click()



On Error GoTo ErrorHandler

Dim myCurrentDir As String
Dim myReportOutput As String
Dim myMessage As String

myCurrentDir = CurrentProject.Path & "H:\"
myReportDir = myCurrentDir & "Reports" & "\"
myReportOutput = myReportDir & "past due invoices" & ".pdf"

DoCmd.OutputTo acOutputReport, [rptpastdueinvoicesreport].[past due], _
acFormatPDF, myReportOutput, , , , acExportQualityPrint

myMessage = "Report generated inside " & myReportDir
MsgBox myMessage, vbInformation

Done:
Exit Sub

ErrorHandler:
MsgBox Error$
Resume Done

End Sub

 

[vbaInet]

See if this link better explains the command:

http://msdn.microsoft.com/en-us/library/bb238050%28v=office.12%29.aspx

Then if you are still having difficulty, let us know.

 

[speakers_86]

If code is not acting as expected, I usually use message boxes to tell me what the code is doing step by step. So if you were to do that here:

Private Sub Command164_Click()
On Error GoTo ErrorHandler
Dim myCurrentDir As String
Dim myReportOutput As String
Dim myMessage As String
myCurrentDir = CurrentProject.Path & "H:\"

The variable myCurrentDir now contains something like C:\Users\YourName\DocumentsH:\

Of course I am assuming the location of currentproject.path, but surely you can see that this location is not right. What path did you want here?

myReportDir = myCurrentDir & "Reports" & "\"

A message box would tell you that this variable is C:\Users\YourName\DocumentsH:\Reports\

myReportOutput = myReportDir & "past due invoices" & ".pdf"

C:\Users\YourName\DocumentsH:\Reports\pastdueinvoices.pdf
I wonder what operating system you are using!

DoCmd.OutputTo acOutputReport, [rptpastdueinvoicesreport].[past due], _
acFormatPDF, myReportOutput, , , , acExportQualityPrint

Here you are telling Acces to export the report "Past Due" to a pdf, and save it to a nonsensical directory.

myMessage = "Report generated inside " & myReportDir
MsgBox myMessage, vbInformation

This message box should tell you that the value in myReportDir is nonsensical.

To fix this, you need to supply a propery location to save the file to, or don't supply one, and Access will ask you where to save the file at through Windows API.


edit-After reading my own post, it looks like you are trying to save the file to H:\Reports\PastDueInvoices.pdf

Try this

On Error Goto ErrorHandler
Dim strPath as string
strPath="H:\Reports\PastDueInvoices.pdf"
DoCmd.OutputTo acOutputReport, "Past Due", acFormatPDF, strPath, , , , acExportQualityPrint
Exit:
Exit Sub
ErrorHandler:
Msgbox "There was an error export the report to " & strPath
resume exit
End Sub

I typed that freehand, so I apologize if its not right.

 

[all7holly]

Hello Speakers_86,

I think I have a better understanding of the code. Below is the code I think will work, however when I try to run it, I get an error on the myReportDir saying "Compile Error Variable Not Defined".Any idea why? I should tell you that this command is under a button on a form and I am running Windows Server Standard XP 64bit.

Private Sub Command164_Click()

On Error GoTo ErrorHandler

Dim myCurrentDir As String
Dim myReportOutput As String
Dim myMessage As String

myCurrentDir = CurrentProject.Path & "C:\users\hmartinez\desktop\"
myReportDir = myCurrentDir & "Reports" & "\"
myReportOutput = myReportDir & "Past Due Invoices List" & ".pdf"

DoCmd.OutputTo acOutputReport, [Report].[rptpastdueinvoicesreport], _
acFormatPDF, myReportOutput, , , , acExportQualityPrint

myMessage = "Report generated inside " & myReportDir
MsgBox myMessage, vbInformation

Done:
Exit Sub

ErrorHandler:
MsgBox Error$
Resume Done


End Sub

 

[speakers_86]

Usually you will get a variable not defined error when a variable is not defined. That means you did not dim your variable! Look at myReportDir. When did you tell Access what that variable is? Is it a string? An integer? You need to dim it. FYI, the reason Access makes you dim it is because at the top of the VBA document, you have "Option Explicit". This means that Access will not allow variables without you explicitly saying what the format of the variable is. That is a good thing. If you were to remove "Option Explicit", I believe Access would just assign the variable to be a variant. But this is all more than you need to know right now.

On another note, add the following line

Msgbox mycurrentdir

after the line you already have mycurrentdir=...

Run the code.

What do you expect the value of MyCurentDir to be?

What does the message box say the value is?

 

[vbaInet]

Private Sub Command164_Click()
On Error GoTo ErrorHandler
    
    Dim strReportPath As String
    Const strReportDir As String = "C:\users\hmartinez\desktop\Reports\"
    
    strReportPath = strReportDir & "Past Due Invoices List_" & Me.[[COLOR=Red]ProjectNumber[/COLOR]] & ".pdf"
    
    DoCmd.OutputTo acOutputReport, rptpastdueinvoicesreport, _
                   acFormatPDF, strReportPath, , , , acExportQualityPrint

    MsgBox "Report generated inside " & strReportDir, vbInformation
    
Done:
    Exit Sub
    
ErrorHandler:
    MsgBox Error$
    Resume Done

End Sub

where ProjectNumber should be substituted for the name of your project number field.

 

[speakers_86]

Ally, did you add the message box? Do you see where you are trying to save this file to?

 

[all7holly]

Hello Speakers_86,
I have decided to go a different direction, I would like this code to save the current report as pdf to specific folder, if this folder already exists, then pdf will be created, if the folder doesn't exist then the folder will be created and then pdf. The name of this report is “rpt work auth”. I would like to folder to be named with the project number and propertyaddress. Then have the report named as “Work Auth”. I would appreciate any help you could give me.


Private Sub Command77_Click()

'Sets report caption name according to the project number
Reports!rpt work auth.Caption = [project number]


Dim myCurrentDir As String
Dim myProjectDir As String
Dim myProjectOutput As String

'Set directories
myCurrentDir = Left(CurrentDb.[ S R Project Database], Len(CurrentDb.[ S R Project Database]) - Len(Dir(CurrentDb. [S R Project Database])))
myInvoiceDir = myCurrentDir & "project number" & "\" & propertyaddress & "\"
myprojectnumberOutput = myprojectnumberDir & txtprojectNumber & ".pdf"

'Check if directory exists
If Len(Dir(myprojectnumberDir, vbDirectory)) = 0 Then
'Make new one if directory doesn't exist
MkDir myprojectnumberDir
Else
'Save report as PDF and set OutputFile to myprojectnumberOutput
DoCmd.OutputTo acOutputReport, "Work Auth", acFormatPDF, myInvoiceOutput, , , , acExportQualityPrint

SendMessage True, myprojectnumberOutput
End If

Else

End If
End Sub

 

[vbaInet]

I will bow out gracefully. Good luck!

 

[speakers_86]

Don't change this

Public Function Exist(ByVal strPath As String, Optional lngAttribute As Long) As Integer
    'attributes are
    'vbNormal    'vbreadonly    'vbhidden    'vbSystem    'vbVolume  vbdirectory 'vbalias
    'see this for more detail on attributes [url]http://www.techonthenet.com/access/functions/file/dir.php[/url]
    On Error Resume Next
    Exist = Len(Dir(strPath, lngAttribute)) > 0
End Function

Don't change this

Public Sub ExportPDF(strRoot As String, strAddress As String, strProjectNumber As Long, rpt As String)
    On Error GoTo Err_Handler
    Dim strPath As String
    'Root directory of your file
    strPath = strRoot & "\" & strProjectNumber & "\"
    Dim strFile As String
    'Full address of your file
    strFile = strPath & strProjectNumber & " " & strAddress
    'Does the directory exist?  If not, create it
    If Exist(strPath, vbDirectory) = 0 Then MkDir (strPath)
    'Does the file exist?
    If Exist(strFile) Then
        Dim intResponse As Integer
        intResponse = MsgBox("That file already exists!  Would you like to replace it?", vbYesNo, "Error")
        If intResponse = vbYes Then
            Kill (strFile)
        Else
            Exit Sub
        End If
    End If
    DoCmd.OutputTo acOutputReport, rpt, acFormatPDF, strFile, , , , acExportQualityPrint
Exit_Sub:
    Exit Sub
Err_Handler:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Sub
End Sub

This is where you define what the values used in the code are. This you should change.

Private Sub Command77_Click()
call ExportPDF("C:\YourRootFolderHere",me.YourAddressField,Me.YourProjectNumberField, "YourReportName")
end sub

I recommend you read this over and over until it makes sense. I'm guessing that you won't know where to put this code. You can put the first two in their own modules. It does not matter what you call the modules, so long as the name is different from the name of the procedures. I recommend mdlExist and mdlExportPDF. Now, you can call on these modules at any time in your database. Even in completely unrelated events.

 

[all7holly]

Ok I almost have it working perfectly. I just need to know how to have the file path name the folder with the ProjectNumber and PropertyAddress. I think it should to on this line.

strPath = strRoot & "\" & "0" & strProjectNumber & "\"

Any ideas?

 

[all7holly]

Never mind I figured it out. Thank you so very much for you help, it is greatly appreciated.