Solved

Create pdf files using MS Access VBA

Posted on 2009-04-01
18
792 Views
Last Modified: 2013-11-27
I need to write VBA code in ms access 2003 to convert the data from access to pdf.  I am already converting to excel.
0
Comment
Question by:asarda
  • 8
  • 5
  • 5
18 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 24045655
Free ... and everything you need - works great:

http://www.lebans.com/reporttopdf.htm

mx
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24046922
Alternative is to use pdf995. Allows you to produce your reports in pdf format
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21621764.html

Another thing, if you are running Office 2007, there is a option to produce pdf files. Just need the additional free download from microsoft (very small file).
0
 

Author Comment

by:asarda
ID: 24269560
sorry for the extremely late reply, but I had a quick look at that the code is really confusing since I don't need most of it.  I will see what I can do.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 75
ID: 24271727
Actually ... you really don't need to worry about the code per se.  Just set it up per the example and/or readme file ... and you s/b good to go.

mx
0
 

Author Comment

by:asarda
ID: 24317744
ok, I got the code to run but no output, probably because its not a real file.  I can fix that.  But I am concerned that I am running this at work, do you think I will have any issues because of that?
0
 
LVL 75
ID: 24318370
"do you think I will have any issues because of that?"

No, because those two DLL's ... they *do not* need to be Registered ...and should not be registered.  They just need to reside in the same folder as the app.  Basically, they are just library files.

And this is total FreeWare ... so ... no issues I can see.

mx
0
 

Author Comment

by:asarda
ID: 24318415
I am getting no output, but it seems to run without errors.  Does the ini file need to exist here iniFileName = "c:\pdf995\res\pdf995.ini" ?
0
 
LVL 75
ID: 24318521
Sorry ... I thought you were using the Lebans version.  I know nothing about 995.  Disregard my post above.

mx
0
 

Author Comment

by:asarda
ID: 24326149
ok so I have decided to use the 995 since I cannot figure out the other code.  How do I select files from a drive or better yet the excel files are created by ms access then dropped to a drive.  How can I use those files and convert to pdf?
0
 
LVL 75
ID: 24327677
rocki will have to help you with that.

mx
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24333437
Hi asarda, Im a little tied up and have been recently but will help you later on.

Where you have installed it, you need to change the code to reflect it to your path

'Change this line to where you have installed pdf995
iniFileName = "c:\pdf995\res\pdf995.ini"

'Double check this also
syncfile = "c:\documents and settings\all users\application data\pdf995\res\pdfsync.ini"


Now my confusion was that I thought when you wanted to print to pdf, typically from Access one would want to print a report.

From your last statement, it seems you want to print files outside of access. If this is the case then the above code would not work for you. However with pdf995, you can manually print, select pdf995 and enter a filename.

0
 

Author Comment

by:asarda
ID: 24335751
I just copied the code to my own MS Access DB and I can run it from there without any issues.

But I am creating the excel files using MS Access, I use calculations in access that's why, then I drop those files in a drive somewhere.  But I was thinking before I drop the file I can convert it to pdf then drop it.  

The only thing I am thinking is some how creating the excel files and then making a report in access then converting it to pdf all using VBA. But I need to do this 30 times, but I how do I create a report from VBA then convert it to PDF and then delete the report so I create the next? This way I don't have 30 reports in access that I really don't need.



0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24336878
if u look at the pdf995 code, you see it takes in the name of the report you want to print and full path of file of where you want it to go.

Still not clear on order of play here

Create Report in Access
Dump to Excel
Print to PDF from Excel

or

Create Excel File
Dump to Access (how? is it an import?)
Print to PDF from Access


What format are these reports in? Are they just a dump of excel data? Would running a query produce the same result as your report?

0
 

Author Comment

by:asarda
ID: 24337395
I get the report and path part - thanks.

I have the data in Access all I do is run queries to produce a result, but I use VB code to format the data and save it as a excel file.  I cannot use the result produced by access because the file needs to be formatted(has different headings and other stuff in the excel file). I need to take that formatted excel file and create a pdf file.

Order of play
Create Report in Access
Dump results to excel
Print excel file to PDF (snapshot of the layout of the excel file)

I have over 30 files that are located in different folders, so it would be better for me to create the pdf in access and drop the pdf file in specified folder along with the excel file.

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24337587
ok, well that then is different to what I interpreted in your initial question cos I read it as converting data from access to pdf.

pdf995 example given will not work in this case since you want to print from excel but it may be tweaked.

All this is in theory, what I believe may work since both products use vba.

Add that pdf995 code example into a new excel macro, then change the openreport line to this

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Ensure its a macro then use excel automation to run that macro
http://www.mvps.org/access/modules/mdl0007.htm

In order to add a module, use the code here
http://support.microsoft.com/kb/194611

So maybe when you output the access query to excel, the automation you must be using to format, add in the addmodule bit, then call it to print?



Or better still, possibly, just possibly

you need to break up the code in pdf995 example

part 1 - set default printer to pdf995 and set filename path (code up to docmd.openreport)
part 2 - run this as part of your automation from the excel object
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
part 3 - set default printer back to what is was (code after docmd.openreport)

0
 

Author Comment

by:asarda
ID: 24338698
Is this possible if I used http://www.lebans.com/reporttopdf.htm version instead?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24340112
I havent used that, need to wait to see what database mx says
0
 

Author Closing Comment

by:asarda
ID: 31565604
Solution gave me a start
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question