Solved

Create pdf files using MS Access VBA

Posted on 2009-04-01
18
800 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

737 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