UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

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



Posts: 146
Joined: 28-January 04
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,395
Joined: 27-February 07
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
Joined: 5-December 02



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
Joined: 23-September 04
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
Joined: 5-December 02



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
Joined: 23-September 04
From: NC, USA


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



Posts: 146
Joined: 28-January 04
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,232
Joined: 19-August 03
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,395
Joined: 27-February 07
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,232
Joined: 19-August 03
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,395
Joined: 27-February 07
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



Posts: 22
Joined: 19-June 09



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,395
Joined: 27-February 07
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



Posts: 60
Joined: 22-October 03



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,395
Joined: 27-February 07
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



Posts: 60
Joined: 22-October 03



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



Posts: 60
Joined: 22-October 03



..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,395
Joined: 27-February 07
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



Posts: 60
Joined: 22-October 03



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,395
Joined: 27-February 07
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 >


RSSSearch   Top   Lo-Fi    7th July 2015 - 04:54 PM