Create pdf files using MS Access VBA

Alternative is to use pdf995. Allows you to produce your reports in pdf format
https://www.experts-exchange.com/questions/21621764/Output-report-to-PDF-and-send-to-email.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).

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.

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

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?

"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

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" ?

Sorry ... I thought you were using the Lebans version.  I know nothing about 995.  Disregard my post above.

mx

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?

rocki will have to help you with that.

mx

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.

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.

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?

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.

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)

Is this possible if I used http://www.lebans.com/reporttopdf.htm version instead?

I havent used that, need to wait to see what database mx says

Solution gave me a start