UtterAccess.com
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:

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?

I tried this:

Reports.Item("Report1").ExportAsFixedFormat Type:=accTypePDF, FileName:="c:Test.pdf", OpenAfterPublish:=True

That just gave me an unhelpful error message.


--------------------
Thom Rose
I can do it fast. I can do it cheap. I can do it well. Pick any two.
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


--------------------
Regards
Paul Churchill, Barton-under-Needwood, Staffordshire, England
A Fine Is A Tax For Doing Wrong - A Tax Is A Fine For Doing Well !!
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.


--------------------

http://www.access-freak.com
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


--------------------

http://www.access-freak.com
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,

Changing 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.


--------------------
Thom Rose
I can do it fast. I can do it cheap. I can do it well. Pick any two.
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 :-)


--------------------
Beer, natures brain defragging tool.
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.

I think that Wildbird's point is well made!


--------------------
Regards
Paul Churchill, Barton-under-Needwood, Staffordshire, England
A Fine Is A Tax For Doing Wrong - A Tax Is A Fine For Doing Well !!
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)


--------------------
Beer, natures brain defragging tool.
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.


--------------------
Regards
Paul Churchill, Barton-under-Needwood, Staffordshire, England
A Fine Is A Tax For Doing Wrong - A Tax Is A Fine For Doing Well !!
Go to the top of the page
 
+
BryanUSB
post Jun 24 2009, 01:40 PM
Post #12

UtterAccess Member
Posts: 23



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)


--------------------
Regards
Paul Churchill, Barton-under-Needwood, Staffordshire, England
A Fine Is A Tax For Doing Wrong - A Tax Is A Fine For Doing Well !!
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:

1. 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


--------------------
Regards
Paul Churchill, Barton-under-Needwood, Staffordshire, England
A Fine Is A Tax For Doing Wrong - A Tax Is A Fine For Doing Well !!
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 could 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

As to the STMP mail, I'm sorry to say that I have no experience of doing that


--------------------
Regards
Paul Churchill, Barton-under-Needwood, Staffordshire, England
A Fine Is A Tax For Doing Wrong - A Tax Is A Fine For Doing Well !!
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,

As 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


--------------------
Regards
Paul Churchill, Barton-under-Needwood, Staffordshire, England
A Fine Is A Tax For Doing Wrong - A Tax Is A Fine For Doing Well !!
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: 24th July 2014 - 07:25 AM