VBA to print to PDF

Hello, first off I am a new member and this my first post. I did a search and found a few results but haven't been able to stitch them together.

So here is what I am trying to accomplish. I am using Access 2010. I import data into a table with numerous records. I then run a report that puts the records into a standard form. Currently when the form creates it lumps them into a multipage document. What I would like to do is have a code that will print to single page PDF. Whats more I would like it to name the document using the date a record entry of the persons name. Lastly save said PDFs to a designated folder.

Variables:

Table name: Phone
Record to name PDF: Agent Evaluated
Report name: Phone eval

Any help would be highly appreciated. Thank you all for your time.


Post your answer or comment

comments powered by Disqus
Hello, first off I am a new member and this my first post. I did a search and found a few results but haven't been able to stitch them together.

So here is what I am trying to accomplish. I am using Access 2010. I import data into a table with numerous records. I then run a report that puts the records into a standard form. Currently when the form creates it lumps them into a multipage document. What I would like to do is have a code that will print to single page PDF. Whats more I would like it to name the document using the date a record entry of the persons name. Lastly save said PDFs to a designated folder.

Variables:

Table name: Phone
Record to name PDF: Agent Evaluated
Report name: Phone eval

Any help would be highly appreciated. Thank you all for your time.

I posted this to another section and then noticed this on dealing with VBA so am repeating it here. I apologize for the duplication. Problem below:

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

I am new to this forum, and new to VBA programming in MS Access. I am trying to print a report in duplex without getting way over my head in complicated coding and not having much success. I have tried:

DoCmd.OpenReport strReport acViewPreview
DoCmd.RunCommand acCmdPrint

which works but isn't practical as users are printing entire books consisting of many reports and the dialogue box pops up for each report. I guess it could work if anyone knows how to supress it after the first time.

Alternatively, I have tried:

DoCmd.OpenReport strReport acViewDesign, , , acHidden
Application.Printer.Duplex = acPRDPVertical
DoCmd.OpenReport strReport acViewNormal

which seems like it SHOULD work, but doesn't.

I have read until I'm bleary-eyed...does anyone have any suggestions?

Thanks so much!

Hello Forum,

There is a certain web page I need to periodically pdf. Rather than me open it up (always the same web address), select distiller, save as the same old filename - could I use vba to automate this process?

(I know it sounds lazy - but there is a perfectly good reason why I would need something like this).

Any help would be greatly appreciated!

-Sean

I need to write VBA to retrieve 2 files and print out in one single output A4 paper size. Can anyone assist on this matter ?

Anyone out there using VB and iTextSharp for generating PDFs inside a VB program?

I have a question with printing the pdf doc once it is made. Is it possible to call the pdf to print through VB or even with the iTextSharp lib?

I have found many forums where the same question is asked but most of the stuff is old and based on other languages. One of the biggest issues on these boards is whether PDF Reader auto closes. I don't care if my EUs have to click an "X" and would actually prefer them to have to interact with the Reader so that they can choose their network printer.

iTextSharp use to have a pretty good forum and website until about 3 weeks ago. They locked it all down and topics are hard to find.

Any help is appreciated.

Hello all,
A quick question which is probably beneath most people on the forum...
I have created a command button on a form, which I would like to print the specific form on which the button is clicked, not every record in the database! I would like a command which therefore filters that record by its OrderID and then executes the DoCmd - Print (or whatever the damn magic spell is!). Also, if I wished to print a report which is based on said OrderID, can this be done in , say, less than 3 pages of code, or should I enroll myself on a "VB for brainless morons" course? I believe they may run these at my local college.
Thanks in advance,

Nick

I print an invoice by opening a formatted Access Report that uses some "On Load" events to select appropriate graphics and notations on the invoice (different logos and such). I currently manually print this to PDF (via PDFCreator) and file the PDF in an "Invoice" folder, giving it the Invocie Number as it's docuemnt name, e.g. 12345.pdf I can then fire an email via VBA to Outlook and pick up the PDF version of the invoice from the Invoice Folder, attaching it to the email.

THis all works fine, but I would like to automate it a little more. Instead of manually printing the PDF file from the Access Invoice document, I would like to initiate the print via VBA and have VBA tell the system what folder to save the PDF to, and then create the email and attach the PDF Invoice.

I have most of the steps already in place, but can any one help me with the VBA code that will open the Access Report (the invoice), load the "on-load event items, print it to PDF (via PDFCreator) and save the PDF in a designated folder on the C:Drive.

I have already ticked the PDFCreator item in the Tools - References section of the code window.

How can I print a report from VBA?
For example I have a report named rpt1, and I want to print'it from code, not after opening the report in preview mode?
Thanks,
Richard

Hi all,
I have an invoice report that page breaks by client(client grouping, force new page). Currently I print and mail these. I'd like to be able to email these to the clients (about 300). Doing so would require two things:

1) Ability to print programmatically to pdf
2) Saving each page as a unique pdf, with the client name in the file name

A nice to have would be for each file to be in a separate folder...but that's nothing important.

Thanks!!

I want to allow the users to click a button and have access bypass displaying the report, but just simply kick off the report in a .PDF format.

I know there's built in functionaility to do this within Access... but how do I do this through code?

I know this will display the Report:
DoCmd.OpenReport "00100_Rpt_Document", acViewPreview, , , , strArg

but how do I get access to print a pdf?

Thanks,
Gary Black

Our office runs MS Access 2003. I’ve been working on a little database. It’s what you could probably call a “skills” database. A report on the different skill sets is generated from the database and uploaded to the web. Prior to my volunteering to automate all the work that was being done manually people were editing separate look a like word documents, printing to PDF and uploading to the intranet. A lot of it seemed like a waste of time to me. Me and my big mouth.

Up until a short while ago I was stumped as to how I could;

1. Print each group to a separate file, and;
2. Print the files as PDF.
I worked out how to output the reports as RTF (though not separately, but as everyone will no doubt know you lose all your report formatting (lines, graphics, etc) when outputting your report as RTF. It wasn’t good enough.

I hit on the idea of hacking together the code that let me print to RTF and the lebans code that lets you print a report to PDF. This is how I did it;

Ingredients;

Files
dynapdf.dll
StrStorage.dll
And Module modReportToPDF
from http://www.lebans.com/

An access database with a query based report that is grouped on one particular data item. Lets call it DB1.
A form (menu or otherwise) that could hold a combobox and a command button . Lets call it FormA.


Procedure

1. Copy dll files to same folder as the database or system folder as described on http://www.lebans.com/
2. Copy Module modReportToPDF to the module library of database DB1
3. Create a global string variable for use as the individual file names. Lets call it GroupVar

e.g - Global Groupvar as String

4. Create an unbound combobox on FormA. Lets call it cboCombo0
Rowsource Type = Table/Query
Rowsource = SELECT TABLE.iNDEXFIELDNAME, TABLE.GROUPFIELDNAME (IF NOT INDEX FIELD) FROM TABLE ORDER BY [GROUPFIELDNAME];
If Index field is the field your report is grouped on then the bound column would be 1. If it’s a second field then the bound column would be 2.

5. Put a command button on Form A (lets call it cmdReportGen.
6. Open your report in Design View
7. Make a note of the name of the field that displays your group name
8. In the “On Format” event for the section of the report within which your group field is place the following; Groupvar = fieldname
9. Place the following code (appropriately amended) against the “On Click” event of cmdReportGen;

Private Sub cmdReportGen_Click()
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
'Adapted to print multiple PDF files based on report grouping
'and save files named for report groups - Stephan 15 September 2010

Dim intCounter As Integer
Dim cboCode As ComboBox
Dim blRet As Boolean
Dim stDocName As String

stDocName = "ReportName"

Set cboCode = Me![cboCombo0]

'If Your Data Type is a String
For intCounter = 0 To cboCode.ListCount - 1
DoCmd.OpenReport stDocName, acViewPreview, , "[Field name for Report grouping] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
blRet = ConvertReportToPDF(stDocName, vbNullString, _
"D:Temp" & GroupVar & ".pdf", False, True, 150, "", "", 0, 0, 0)
'The variable "GroupVar" has beeen declared globally and is initialised at the "OnFormat" event of report generation
'It's then passed to the file name here and cleared just before the end of each loop
GroupVar = ""
DoCmd.Close acReport, stDocName
Next

MsgBox "Your files have been saved to D:temp with individual category names", vbOKOnly

End Sub


There’s no error control and it’s hacked to bits but it works. I hope somebody else can use it.

Cheers
Stephan

Hi.

I'm almost finished with my big project! At least, the big stuff! I'm creating the menus now, soon will be modifying the back-end data tables to match the changes I've made in my test bed, and securing it all, finishing the user manual and conducting some training.

My question at this point: we have PDF sheets with supporting information; one of each of them must be printed along with all the other reports. Not every PDF sheet is going to be for a product in a particular store, though. In one of my tables, I have a field that links to the PDF for each product.

I believe the code is going to be

SELECT Distinct MSDS
FROM Product;

So that part is easy.

My question is...can I even do this? I thought at first that I would just have to create an empty report with this code but I realize that's just the beginning. This report needs no formatting or anything. I just need the vehicle to print one, and only one, copy of each PDF (some of the PDFS attach to a couple of hundred products...only need 1 copy of each one, not hundreds - hence the Distince MSDS in the code above), but only the PDFS that apply to that particular store.

Is there a better way to do this?

Ok so here is the skinny...

I have a form that I need to print multiple times, but to one file such as a PDF or XPS (ideally the file-type is up to the user).

I need to be able to print it as a form, I can't use the same information in a report because of a function that only works in the form.

I just need to be pointed in the right direction. I have looked into a number of options such as using PDFCreator, but I plan to distribute the program, and even though it is "open source" there are restrictions on redistribution.

I have the microsoft PDF plugin installed, and using this would be ideal.

What I would like to see is something that caches each "printed" document and then takes the entire cache and uses DoCmd.OutputTo to parse it to one file.


Thanks for the help

I am currently using the following statement from within Access VBA to print records to a file:

Print #FF, DLookup("[Column1]", "Some Table")

This part has worked. For the next step I would like to print the next records in the table, starting with the first record and ending with the last. How is this done? How do I tell Access through VBA that I want the next record? Once I understand this, I could then wrap a loop around it and extract the remaining records.

NOTE: I can not use the ".DoCmd.TransferText" because I need to add a header and trailer to this file.

Any simple example would be helpful.

Thanks.

Hi All,

I have access database with a form which gets open directly when I open the file. The form has two Comboboxes on it to select the parameters once we select the parameters we can click on the print button and then it opens the report is preview and then we need to hit ctrl + p and print it to pdf there are hundreds of such reports so I want to come up with a code which will print all reports automatically to pdf and then save them on a specific path on local drive. The main issue is there are no separate reports there is one report and then code is querying data on the same report as per the parameters selected by user on the form.

combo boxes gets populated with queries and they are interlinked with query for e.g. Combobox1 gets populated with region and combobox2 gets populated with the country in that region so if you select "Asia" Region in Combobox1 then Combobox2 will get populated with countries like "Singapore", "Japan", "Hongkong" etc. Now you will have to select one of the country and then print report and need to do the same exercise for all regions. Now this means that whenever the selection in combobox1 selection changes the combobox2 requeries it's data in order to populate the respective region countries in it.I want the code to print the files to pdf using "pdfcreator" as printer and then save the files on a specfic path like "C:".

Below is the code which I have worked on so far but it shows an error that is Run-time error '3061': Too few parameters. Expected 1:


	Code:
	Private Sub Commandbutton_Click ()
Dim rst As DAO.Recordset
Dim CustomerID As Long
Set rst = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)
Debug.Print rst
With rst
    Do Until .EOF
        ProgramID = !ProgramID
        DoCmd.OpenReport "StrptReport", acViewPreview, , "CustomerID=" & CustomerID
       Reports![StrptReport].Caption = [FullReportName]
        Name "StrptReport" As [FullReportName] & ".pdf"
        .MoveNext
    Loop
    .Close
End With
Set rst = Nothing
End sub

When I click on the debug error It highlights the below line from code:

	Code:
	Set rst = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)


Thanks a lot for your help in advance.

Hello. I'm not very good at using VBA, but I've managed to write a code that saves the current report as pdf to specific folder, if this folder already exists, then pdf will be created, if folder doesn't exist then folder will be created and then pdf, which will be attached to Outlook mail after.

It's somehow working ok, but sometimes when I execute it, it creates the specific folder, but doesn't save the pdf as I get OutputTo error ' Microsoft Access can't save the output data to the file you've selected.'

I can't find where is the problem or is it correct even in the code, because it is working sometimes.


	Code:
	Private Sub cmdEmail_Click()

'Sets report caption name according to the invoice number
Reports!Invoice.Caption = [txtInvoiceNumber]

'Check if e-mail is set for a client
If Not IsNull([txtEmail]) Then

    Dim myCurrentDir As String
    Dim myInvoiceDir As String
    Dim myInvoiceOutput As String

    'Set directories
    myCurrentDir = Left(CurrentDb.Name, Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name)))
    myInvoiceDir = myCurrentDir & "Invoices" & "" & txtClient & ""
    myInvoiceOutput = myInvoiceDir & txtInvoiceNumber & ".pdf"
    
    'Check if directory exists
    If Len(Dir(myInvoiceDir, vbDirectory)) = 0 Then
        'Make new one if directory doesn't exist
        MkDir myInvoiceDir
    Else
        'Save report as PDF and set OutputFile to myInvoiceOutput
        DoCmd.OutputTo acOutputReport, [Report].[Name], acFormatPDF, myInvoiceOutput, , , , acExportQualityPrint
        'Execute Outlook SendMessage function with attachment path myInvoiceOutput
        SendMessage True, myInvoiceOutput
    End If

    Else
        MsgBox ("No E-mail address available for this client")

End If
End Sub

Please I hope someone can help me with this. Thanks!

Hi all,

Please help me with the issue mentioned in the subject. I have a report in an access database which has about 400 pages which I have to split and export to multiple pdf files.
In the same database I have a table with 3 columns:
- the first column contains the first page of the pdf file
- the second column contains the last page of the pdf file
- the last column contains the title of the pdf file

Eg: the report will be split in 3 pdf files:
- one that starts at page 1(first column of the table) and continues to page 112(second column of the table) and is named Document1.pdf(3rd column of the table)
- the second starts at page 113, continues to page 350 and is named Document2.pdf
- the third starts at page 351 until page 400 and is named Document3.pdf


Many thanks!

Heres the situation, I have a GUI that the end user enters the data into. When they have completed the information, they click on a PRINT button to generate the report. I have modled the report from an issued form (PDF) that we are suppossed to use. My question, is there a known way to print the PDF file as well as the report from the click of a single button? I would like to trigger the printing of the a single page PDF file, my report, then the second PDF file containing the remainder of the original document.
I have attempted many things and had some unsuccessful (but interesting) results. Now I fear my last hope lies in code, my weaker point.
Any suggestions?

Since there is no introduction section, I'll just say a quick 'Hi' to all in here...

Ok, I hadn't used Access until 2 months ago, and had never seen VBA until 2 weeks ago, so that sets the standard I'm at I suppose.

The problem is my daughter and I have both been toiling trying to get the final project done for her postgrad, and the last thing she wanted was an MS Access project, but that's what she got.

Hard to explain without seeing it, but basically she was handed and old-ish database which was saving files from a form to the A: drive, plus a couple of other problems which we've managed to sort out.

I've managed to replace the automatic output to the A: drive with a 'Browse' dialog, and the files are now output to the folder selected. The only problem is that they want them to print out at the same time, and we just don't know how to get the code to do it.

I've attached the database (it's quite simple and really small) and the code is behind the two buttons marked 'Publish To File' and 'Publish With Abbreviated Institution,' both of which are on the initial form that opens.

I feel like we've come so far and learned so much (the hard way) but are now stuck at the final hurdle. Can anyone please help out?

Ok well what I am trying to do is use VBA code to save an Acrobat Version 7.0 PDF into a text file so it can be pulled into a database using monarch. The actual saving of the file as text works, and here is the code I have used to accomplish that:

Dim AcroXApp As Object
Dim AcroXAVDoc As Object
Dim AcroXPDDoc As Object

Set AcroXApp = CreateObject("AcroExch.App")
AcroXApp.Hide

Set AcroXAVDoc = CreateObject("AcroExch.AVDoc")
AcroXAVDoc.Open PDF_PATH & filename, "Acrobat"

AcroXAVDoc.BringToFront

Set AcroXPDDoc = AcroXAVDoc.GetPDDoc

Dim jsObj As Object
Set jsObj = AcroXPDDoc.GetJSObject

jsObj.SaveAs OUTPUT_PATH & OutputFile, "com.adobe.acrobat.plain-text"

AcroXAVDoc.Close False
AcroXApp.Hide
AcroXApp.Exit

Now as I said the code itself runs perfectly and saves the pdf as a plain text file. The problem I am having is that the original pdf is in neat column format, but the resulting text file loses all the whitespace and creates only a single space between column fields. This is insufficient for my needs as some of the fields have spaces in them, so i cannot use a space-delimited method to import them.
My current work-around is that rather than save the file as text, i save it as a html version 3.20 file by changing "com.adobe.acrobat.plain-text" to "com.adobe.acrobat.html-3-20 "and i use monarch to anaylze that file. This works mostly, but it has had many bugs to work out because of inconsistencies in the way acrobat saves the file as html.
The application i've built does work for the most part, but i am concerned that if it receives some odd data that it will break because as it is built it is somewhat volatile. I need 7 monarch models as it is just to gather all of the data correctly.
I was wondering if there was any way to save the pdf file as a text file but still retain the column format and whitespace from the original pdf. This would alleviate most all of my problems. Also I use Acrobat version 6.0 standard, Monarch Pro 6.00 and MS Access 2000. I appreciate any help anyone could give me.

hi, i am trying to create a macro that automatically opens, then prints a pdf object from within a excel spreadsheet. i can open the file but i cannot manage to get it to print, can anybody help, pleeeeeease!!

I want my page layout for the Form as landscape. Does anybody knows how to do it in VBA before printing the form runtime?
[I checked LayoutForPrint property, but its for fonts, and not for page layout.]

Hi All,

I just got in to a another problem with my report and I hope one of you kind people can help me to solve this problem. I have created a access report for a invoice and I have placed few command buttons on the report header like Print to PDF , Print to Invoice and Print Preview. Well, first two buttons working fine and I am stuck with the third button. I want to convert the already open report (in Report view) to print preview. how can I achieve this using VBA. Please Help

Help needed!!
I have report created in Access 2010 originated from a querybased on a table.
My table is called: tblFiledReconn
In order to manipulate what data I want to see in the reportI created a list query called: RptQry_List_Table_For_Entech_Use
Finally I have a report based on this query called:FieldReconnFormReport

There is a field on my table called FacilityID.
What I need is to print a report for each FacilityID into anindividual PDF file containing the FacilityID as part of the file name. At themoment this would be 817 individual reports.

I also wish if I can add a date included on the report whichis on another field called: Date. But again this is a wish and not a must.Since I am not an avid code writer I decided to get the need accomplishedbefore attempting this. Since the Date field is a date, I guess I need toconvert it into string or numbers to the format I desire before adding it tothe name which would made the code more complex.
Searching and reading forums I was able to develop thefollowing code. In this attempt I was trying to accomplish the following.
For each facility ID print a pdf individual report with thefacility id as part of the name followed by "_FRECON.pdf". Forexample for facility ID wwMH84303 the report file name would bewwMH84303_FRECON.pdf
Here is the code:
Code: Private Sub Command0_Click() Dim MyDB As DAO.Database Dim MyRS As DAO.Recordset Dim strSQL As String Dim strRptName As String Dim count As Integer strRptName = "FieldReconnFormReport" strSQL = "Select tblFieldReconn.[FacilityID] FromRptQry_List_Table_For_Entech_Use;" Set MyDB = CurrentDb Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly) With MyRS Do While Not MyRS.EOF DoCmd.OpenReport strRptName, acViewPreview, ,"[FacilityID]=" & ![FacilityID] DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF,"C:Temporary FR Forms" & ![FacilityID] &"_FRECON.pdf" DoCmd.Close acReport, strRptName, acSaveNo .MoveNext Loop End With MyRS.Close Set MyRS = Nothing End Sub The problem is when I run the code there seems to be aproblem with the openreport comand line. A window comes out requesting aparameter value. The Enter Parameter Value window shows the Current Facility IDand a text box. If I hit OK it creates a one page report with blank fields butwith the correct file name. If I type the current Facility Id on the text boxit creates the one page report correctly. So I my guess is that it is notunderstanding the instruction that matches the Facility ID in the report withthe corresponding Facility ID from the recordset ( "[FacilityID]="& ![FacilityID]). Since it does creates the correct file name I assume itis creating the desired recordset. I have spent several hours trying to get itwork but no success. Please help!
Now to the wish part:
As I explained there is also a Date field in the table(,query and report). It would be great if the created file name could be acombination of both. For example for FacilityID: wwMH98765 visited ofDate:11/20/2012 it would be great is the file name could bewwMH98765_FR112012.pdf or better if wwMH98765_FR121120.pdf (year,month,day)
Thanks in advance


Not finding an answer? Try a Google search.