UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

2 Pages V   1 2 >  
Reply to this topicStart new topic
> Export report to PDF using VBA    
 
   
thomrosewwb
post Jun 23 2009, 10:25 AM
Post #1

UtterAccess Addict
Posts: 146
From: Seattle, Washington, USA



In Excel, I found this code which works great to print a worksheet to a PDF file:
!--coloro:blue-->Sheets.Item("Sheet1").ExportAsFixedFormat Type:=xlTypePDF, FileName:=strJEFile, OpenAfterPublish:=True
What is the code to do the same thing to export an Access report to PDF?
Otried this:
Reports.Item("Report1").ExportAsFixedFormat Type:=accTypePDF, FileName:="c:Test.pdf", OpenAfterPublish:=True
That just gave me an unhelpful error message.
Go to the top of the page
 
+
Paul_Churchill
post Jun 23 2009, 10:48 AM
Post #2

UtterAccess VIP
Posts: 1,377
From: Barton-under-Needwood, Staffordshire, England



DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, PdfFileNameToStore, False works in one of my DBs so first remove one of the 'c's fron the Type
Go to the top of the page
 
+
Bob_L
post Jun 23 2009, 11:05 AM
Post #3

Utterly Banned
Posts: 7,038



Make sure you have Office 2007 Service Pack 2 installed otherwise the export to PDF from a report is not available.
Go to the top of the page
 
+
freakazeud
post Jun 23 2009, 11:42 AM
Post #4

UtterAccess VIP
Posts: 31,413
From: NC, USA



Huh? Are you sure...it has always been available if you have the plug in installed. All SP2 did was to automatically install it if it wasn't already added. The other feature addition that SP2 brought is to be able to export reports to the Excel format again but that has nothing to do with PDF.
Go to the top of the page
 
+
Bob_L
post Jun 23 2009, 11:48 AM
Post #5

Utterly Banned
Posts: 7,038



Yep, got that mixed up (brain is working intermittently).
Go to the top of the page
 
+
freakazeud
post Jun 23 2009, 11:54 AM
Post #6

UtterAccess VIP
Posts: 31,413
From: NC, USA



thumbup.gif
Go to the top of the page
 
+
thomrosewwb
post Jun 23 2009, 05:36 PM
Post #7

UtterAccess Addict
Posts: 146
From: Seattle, Washington, USA



Thanks, Paul,
hanging my code to use "ac" instead of "acc" did not help. So, I took the easy way, and used your DoCmd.OutputTo. Works fine now.
Go to the top of the page
 
+
WildBird
post Jun 23 2009, 09:07 PM
Post #8

UtterAccess VIP
Posts: 3,121
From: Perth, Australia



ExportAsFixedFormat is Excel, not Access code, so wouldnt work. Alot of things available in Excel may be available in Access, but syntax is usually always different (just to make it more fun for us :-)
Go to the top of the page
 
+
Paul_Churchill
post Jun 24 2009, 02:14 AM
Post #9

UtterAccess VIP
Posts: 1,377
From: Barton-under-Needwood, Staffordshire, England



Glad that we could help. Thank you for the feedback.
think that Wildbird's point is well made!
Go to the top of the page
 
+
WildBird
post Jun 24 2009, 02:23 AM
Post #10

UtterAccess VIP
Posts: 3,121
From: Perth, Australia



Also, in cases like this, if you hit F2 in the code window, it will bring up Object Browser and you can search there for words like ExportAsFixedFormat and will show what library it is in (if you have Excel referenced, will show it being in Excel, otherwise probably be blank)
Go to the top of the page
 
+
Paul_Churchill
post Jun 24 2009, 02:59 AM
Post #11

UtterAccess VIP
Posts: 1,377
From: Barton-under-Needwood, Staffordshire, England



Must get my brain defragged this evening to help with the storage and recall of all these morsels of knowledge!

What with the time difference Wildbird is already at defrag time
Edited by: Paul_Churchill on Wed Jun 24 4:02:23 EDT 2009.
Go to the top of the page
 
+
BryanUSB
post Jun 24 2009, 01:40 PM
Post #12

UtterAccess Member
Posts: 22



I get a compile error on acFormatPDF ("variable not defined") when using the code sample provided by Paul Churchill. I'm using Office 2003. Must I have Office 2007 SP2 installed to use the export to PDF option? If not, is there a reference I can add to solve this?
Go to the top of the page
 
+
Paul_Churchill
post Jun 24 2009, 02:31 PM
Post #13

UtterAccess VIP
Posts: 1,377
From: Barton-under-Needwood, Staffordshire, England



Export to pdf is a Microsoft add in for A2007 in A2003 you will have to use Lebens code (search UA for it)
Go to the top of the page
 
+
tormod
post Jul 6 2009, 10:54 AM
Post #14

UtterAccess Enthusiast
Posts: 60



The
CODE
DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, PdfFileNameToStore
is working perfect, but 2 questions:
. Could I set a predefined pdf filename and location without getting the "save as" dialog?
2. If ypu kan predefine the pdf name, could it get a value from a table/queries? Like name "log week" and the get week number from a table so output is like this: "log week 27.pdf"?
Best regards
Tormod
Go to the top of the page
 
+
Paul_Churchill
post Jul 6 2009, 12:34 PM
Post #15

UtterAccess VIP
Posts: 1,377
From: Barton-under-Needwood, Staffordshire, England



Yes & Yes
Here is the email button code from my Invoicing database
At this point I've already created the invoice , so its sitting in 2 tables (header and detail rows) and in a form Ive just selected the invoice number to send by email (Thunderbird actually).
The code will create and name the pdf in an invoices 'sent' directory and then create the email with the pdf attached.
Notes:
1) I pack the invoice numbers out with leading zeros so that theu sort naturally in the directory when viewed in windows explorer
2) the second column of the combo that I used ti select the invoice holds the customers name which will get appended to the pdf name to make it read something like "0021-CustomerName.pfd"
CODE

Private Sub btnEmailInvoice_Click()
On Error GoTo Err_btnEmailInvoice_Click
Dim PdfFileNameToStore As String
Dim PdfFileNameForEmail As String
Dim strLeadingZeros As String
    If IsNull(Me.CboInvoiceNumberSelected) Then
        Call MsgBox("No Invoice Number Selected" _
                    & vbCrLf & "" _
                    & vbCrLf & "Please select an Invoice number and try again" _
                    & vbCrLf & "" _
                    , vbCritical Or vbSystemModal, "Data Validation")
        Exit Sub
        
    
    Else
        PubInvoiceNumberToPreview = Me.CboInvoiceNumberSelected
        Select Case Len(Me.CboInvoiceNumberSelected)
            Case Is = 2
                strLeadingZeros = "00"
            Case Is = 3
                strLeadingZeros = "0"
            Case Is = 4
                strLeadingZeros = ""
        End Select
        
        PdfFileNameToStore = "C:\_CMC_Ltd\Invoices Issued\Sent\" & strLeadingZeros & Me.CboInvoiceNumberSelected & "-" & Me.CboInvoiceNumberSelected.Column(1) & ".pdf"
        PdfFileNameForEmail = "C:/_CMC_Ltd/Invoices Issued/Sent/" & strLeadingZeros & Me.CboInvoiceNumberSelected & "-" & Me.CboInvoiceNumberSelected.Column(1) & ".pdf"
    
        Dim stDocName As String
    
        stDocName = "Invoice"
        DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, PdfFileNameToStore, False
    Call EmailViaThunderbird(PdfFileNameForEmail, Me.CboInvoiceNumberSelected.Column(2), Me.CboInvoiceNumberSelected.Column(3))
        
    End If
Exit_btnEmailInvoice_Click:
    Exit Sub
Err_btnEmailInvoice_Click:
    MsgBox Err.Description
    Resume Exit_btnEmailInvoice_Click
    
End Sub
Go to the top of the page
 
+
tormod
post Jul 6 2009, 04:12 PM
Post #16

UtterAccess Enthusiast
Posts: 60



Just fantastic Paul!
I have to take a closer look at the email part, I actually have asked a question about that early today.
A lot of my users use webmail, and run the db in access runtime..
So if I could bother you with taking a look at:
Re: SMTP to send email question
I want be that you may help me with that to..?
Best regards
Tormod
Go to the top of the page
 
+
tormod
post Jul 6 2009, 06:08 PM
Post #17

UtterAccess Enthusiast
Posts: 60



..and.. (I'm sorry for asking so much, but I can't code :( )
How do I delete a file on a given location?
I've seen the DoCmd.DeleteObject but I'm guessing from the VBA help that this only is INSIDE the db file..?
And can i du it like on delete on move files to folders. or rename them?
Once more, sorry to asking!
Tormod
Go to the top of the page
 
+
Paul_Churchill
post Jul 7 2009, 03:15 AM
Post #18

UtterAccess VIP
Posts: 1,377
From: Barton-under-Needwood, Staffordshire, England



For the VBA file manipulation see the examples here
Is to the STMP mail, I'm sorry to say that I have no experience of doing that
Go to the top of the page
 
+
tormod
post Jul 7 2009, 03:32 AM
Post #19

UtterAccess Enthusiast
Posts: 60



Hi Paul!
Thank you, I'll take a look at the at that link sad.gif
Have a wonderfull day,
Tormod
Go to the top of the page
 
+
Paul_Churchill
post Jul 7 2009, 07:01 AM
Post #20

UtterAccess VIP
Posts: 1,377
From: Barton-under-Needwood, Staffordshire, England



Thank you,
Is to "Have a wonderfull day" - I had that on Saturday last when I drove a Steam Loco, a full size one otherwise called Big Boys Toys
Go to the top of the page
 
+

2 Pages V   1 2 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 2nd September 2014 - 04:08 AM