Solved

Create pdf files using MS Access VBA

Posted on 2009-04-01
18
762 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 - Access MVP) earned 250 total points
Comment Utility
Free ... and everything you need - works great:

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

mx
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Sorry ... I thought you were using the Lebans version.  I know nothing about 995.  Disregard my post above.

mx
0
 

Author Comment

by:asarda
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
rocki will have to help you with that.

mx
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Is this possible if I used http://www.lebans.com/reporttopdf.htm version instead?
0
 
LVL 65

Expert Comment

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

Author Closing Comment

by:asarda
Comment Utility
Solution gave me a start
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now