Access Reports To Adobe PDF Via VBA code? 2

[HFloyd]

This has had me puzzling around in circles for some time...
How to have a command button on an MS Access form that will Open an MS Access report and "print" it via Adobe PDF Writer without any additional user intervention.

Ideally I would like to be able to specify via my command button code both the directory path and the filename that the PDF file should be saved as, but I am resigned to just getting it to print the file using the report caption (which I have been able to change to the correct filename via the code) then adding additional FileSystemObject code to move the PDF to the correct directory.

In other words my main problem is this: Clicking the "Save" button in the PDF Writer Dialog box that shows up. In another thread (thread705-54219) A user was able to use "SendKeys" with Chr(10) to "click" the Save button (and also to send the filename) but I have not been able to get Sendkeys to send anything to the dialog box.

Does anyone have any insight into this issue?

Thanks,

Heather

http://www.floydinnovations.com

 

[SgtJarrow]

Heather,

See my FAQ in the Reports Forum (faq703-2533). It has full code for saving a report as PDf and allows you to specify the name of the saved file. Note: Users in 9x platforms have noted problems with this code....due to the registry entry changes. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[HFloyd]

Thanks mstrmage1768,

I had already tried code similar to this, but was having problems accessing the correct registry entries.

Your code works perfectly!

Thanks again!

Heather

http://www.floydinnovations.com

 

[yippiekyyay]

This is awesome mstrmage1768!

Would you also have any ideas on how I could open a certain webpage (instead of a report) and pdf it automatically from an Access form?

Thanks!

-Sean

 

[SgtJarrow]

Sean,

Wow! That's a request. At this time no...but I will do some research.

****************************
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[oharab]

Talk about pushing the envelope! It's amazing what people want to do with Access!
Isn't there a web page control thingy that you might be able to use?

Just a thought.

Good luck with this!

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[SgtJarrow]

Ben,

There is a web page control. I go it from somewhere on the web. That was going to be my approach:

Use the web page control to get the appropriate page displayed in Access and basically "snapshot" that into Adobe and see what happens....

Not only is it amazing what people want to do with it Access, it is even more amazing that distributors and sellers of "more powerful" products, such as Oracle, SQL server etc, will constitently tell you you can do the things we are doing with Access.

I just love commercialization!

Oops...sorry bout the .

****************************
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[oharab]

Don't apologise for having an opinion! Perhaps if more of us stood up & shouted about what Access can do as opposed to what it can't, we'd get a bit more respect!




B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[yippiekyyay]

Hey Everyone,

I feel like I started a bit of a debate!

The reason why I'd like to be able to perform such a function is that I ended up having to do one of my reports with Coldfusion because of one small limitation in Access. Now I'm left with a fully functional app with the exception of that one report and I don't want it to be a point of confusion for my co-workers when I move on to my next job.

I think most people do know the reasonable expectations of Access - but I don't think anyone can say for sure where the lid is on the possibilities. Nobody is demanding that I find a way to do this - I just think it'd be cool and would create a learning opportunity for myself. Access is my only option at work, so I like to make the most of it when I can.

Thanks for the response - I'll have time to try out your suggestion later on this week. Sorry for the long post (I just didn't want to be confused for a kind of VBA Script Kiddie)

Thanks again!

-Sean

 

[yippiekyyay]

Hey Guys - sorry for my above post - I think I might've read it out of context!

I ended up defending my point which just agrees with what you were both saying.

Would the explanation that I've had no coffee today suffice?

You are right on the commercialization issue! I took a couple of VB courses and was amazed to find that I could apply most of what I learned there to Access (and I took an Access class before too!). Instead, MS ends up marketing their product to the masses which I think accounts for some of the loss of respect you were mentioning.

-Sean

 

[oharab]

I can relate to the lack of coffee!
Have one from me


B.

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[SgtJarrow]

I think this is not so much a debate as a couple of us just spitting out some info. I find Access to be great. For the most part, anything done in VB can be done in Access. At least as far as I have seen. I mentor a few yonger programmers here and we kind of have a saying....when one of us is stuck, we will turn to another and say, "You know, I think I have just found something that can't be done in Access." Of course, we all know this really just means we need a bit of help, and we have yet to come across something we could not solve. I have developed everything from inventory control databases, HR tracking database, customer service call systems, IT help desk trackin, and more. A couple of POS systems were some of my more enjoyable experiences, and my side work tends to be more enjoyable than the same old dribble I go through at the office. But all in all, Access makes for a pretty robust and complete database system.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[yippiekyyay]

I find myself defending Access as well - here is a great discussion on the same subject you both might be interested in!

http://www.access-programmers.co.uk/forums/showthread.php?threadid=41488

As for my first question - I used the web page control as you guys suggested (which was crypticly called Microsoft Web Browser). I can load up the page I need to be displayed and can print it by using code I found in another post:
WebBrowser.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_PROMPTUSER

My next task will be to select the printer and file name without user intervention. Making the browser not visible and trying to just print it doesn't seem to work - but that's ok because I like the idea of the user being able to browse through the report first. I'll keep everyone posted!

-Sean

 

[btalon]

I've tried using the module from FAQ703-2533 and haven't been able to get it to work. I have Acrobat 5.0, which I thought contains Writer, Am I right? I've tried it with Access 97 on Windows 2000 and Access 2000 on Windows XP. A pdf file is never saved anywhere, and the report cannot be opened from the module because the default printer is changed. Does anyone have any ideas?

 

[yippiekyyay]

Hey btalon,

Have you ever made a pdf before? If not, then print this page using "Distiller" instead of your normal printer. If you do not see Distiller in your list of printers, then I would say that you either don't have Acrobat 5 Writer or the Distiller still needs to be installed from your disk (but I was pretty sure that that should have happened automatically ...?).

If all goes well, then you'll be prompted for a file location and name. And that's it!

Let us know.

-Sean

 

[btalon]

Thanks for the help. As it turns out the name of the Acrobat printer in the module wasn't set to Acrobat Distiller. Is there a parameter that will allow the report to save without a prompt and can you keep Acrobat from opening? I'm trying to automate access reports to email out one at a time, the report being recreated each time?

 

[yippiekyyay]

Yes there is btalon! When you select Distiller, select properties and then Advanced I think. I'm not at work right now and I don't know off-hand (I just remember about 8 check-boxes - 2 of them for what you're asking).

I'll confirm it for you tomorrow morning (unless you figure it out first).

-Sean

 

[SgtJarrow]

The above mentioned FAQ and code will not work with Distiller...you can set the report ot always print to the Distiller as yippiekyyay is suggesting. My FAQ requires Adobe Writer. To be perfectly honest, i don't know if it is included in the Acrobat 5.0 If you downloaded it and installed it from the interent, chances are it does not....the freeware version of Acrobat does not include Writer. I also think you have to install it separately, if I remeber correctly. Without the Writer, you cannot take advantage of the FAQ.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[yippiekyyay]

Hey guys,

I see that I have print drivers for both "PDF Writer" and "Acrobat Distiller" and they both have different options - but the output to me seems pretty much the same.

btalon - if your goal is simply to create a pdf without a prompt for filename and without opening Acrobat afterwards, then:
File - Print - Click once on Acrobat Distiller - Preferences - "Adobe PDF Settings" tab - de-select "View Result in Acrobat" and "Prompt for the PDF Filename".

This will most likely throw the pdf into the "My Documents" folder. I wanted to be able to select a specific folder which is how I ended up on this thread.

Good luck!

-Sean

 

[btalon]

Thanks for the input Sean. The settings won't stay each time I write to the Distiller Printer though. It's always something.