473,327 Members | 1,967 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Create pdf from report using Cutepdf by means of VBA

TheSmileyCoder
2,322 Expert Mod 2GB
I would like whenever a report is created by a user in my db to store the report as a pdf on a network location, without showing/troubling the user.

Anyone know if this can be done, and hopefully how?

Im currently using CutePDF, but any freeware pdf-writer could be an option.


I would need to be able to "feed" the cutePDF writer (or similar) with the desired filename.
Jan 15 '10 #1

✓ answered by ADezii

I wrote an Off-the-Wall routine that should enable you to Auto Fill the Save As Dialog with a Unique Filename and Save it to the destination of your choice. You can change Lines 1, 8, and 11 to suite your specific needs.
Expand|Select|Wrap|Line Numbers
  1. Const conPATH_TO_PDF As String = "C:\Stuff\"
  2. Dim strPathToPDF As String
  3. Dim strReportName As String
  4.  
  5. 'Changes the Default Printer, then resets it to the original Printer.
  6. 'This will only work if the report is set up to Print to the Default
  7. 'Printer
  8. Set Application.Printer = Application.Printers("PDF Converter")
  9.  
  10. 'Report Name assign to Variable
  11. strReportName = "rptPDF"
  12.  
  13. 'Make the *.PDF File Unique
  14. strPathToPDF = conPATH_TO_PDF & strReportName & Format$(Now(), "yyyymmddhhmmss") & ".pdf"
  15.  
  16. DoCmd.OpenReport strReportName, acViewNormal, , , acWindowNormal
  17.  
  18. 'Fill in the Absolute/Unique Path in the Save As Dialog
  19. SendKeys strPathToPDF, True
  20.  
  21. 'TAB to the Save Command Button
  22. SendKeys "{TAB 2}", True
  23.  
  24. 'Simulate Clicking the Save Key
  25. SendKeys "{ENTER}", True
  26. SendKeys "{ENTER}", True
  27.  
  28. 'Reset the Default Printer back to the way it was
  29. Set Application.Printer = Nothing

8 16261
nico5038
3,080 Expert 2GB
From the CutePDF FAQ:
- How to bypass Save As dialog box ,or make unattended installation?
The Custom Edition supports those features.

This custom edition will however cost some $$'s.

Basically you need an application that can be controlled by the SHELL() command.

Nic;o)
Jan 15 '10 #2
ADezii
8,834 Expert 8TB
I wrote an Off-the-Wall routine that should enable you to Auto Fill the Save As Dialog with a Unique Filename and Save it to the destination of your choice. You can change Lines 1, 8, and 11 to suite your specific needs.
Expand|Select|Wrap|Line Numbers
  1. Const conPATH_TO_PDF As String = "C:\Stuff\"
  2. Dim strPathToPDF As String
  3. Dim strReportName As String
  4.  
  5. 'Changes the Default Printer, then resets it to the original Printer.
  6. 'This will only work if the report is set up to Print to the Default
  7. 'Printer
  8. Set Application.Printer = Application.Printers("PDF Converter")
  9.  
  10. 'Report Name assign to Variable
  11. strReportName = "rptPDF"
  12.  
  13. 'Make the *.PDF File Unique
  14. strPathToPDF = conPATH_TO_PDF & strReportName & Format$(Now(), "yyyymmddhhmmss") & ".pdf"
  15.  
  16. DoCmd.OpenReport strReportName, acViewNormal, , , acWindowNormal
  17.  
  18. 'Fill in the Absolute/Unique Path in the Save As Dialog
  19. SendKeys strPathToPDF, True
  20.  
  21. 'TAB to the Save Command Button
  22. SendKeys "{TAB 2}", True
  23.  
  24. 'Simulate Clicking the Save Key
  25. SendKeys "{ENTER}", True
  26. SendKeys "{ENTER}", True
  27.  
  28. 'Reset the Default Printer back to the way it was
  29. Set Application.Printer = Nothing
Jan 16 '10 #3
TheSmileyCoder
2,322 Expert Mod 2GB
NICE!

Simple, but impressive. Looking forward to testing it.
Jan 16 '10 #4
ADezii
8,834 Expert 8TB
It was actually functional on my end, I guess anything is possible! (LOL)

P.S. - The strange part is that the first
Expand|Select|Wrap|Line Numbers
  1. SendKeys "{ENTER}", True
appears to be ignored.
Jan 16 '10 #5
nico5038
3,080 Expert 2GB
I'm a bit reluctant to use the SendKeys, as they might get interrupted / send to another application when the user clicks somewhere on the screen...
There's never a 100% guarantee that it's finished correctly, so you need to make sure your application offers a "re-try".

Nic;o)
Jan 17 '10 #6
ADezii
8,834 Expert 8TB
Hello Nico, I would imagine that the chances of a User clicking somewhere else on the Screen after clicking a Command Button to initiate code execution would be infinitesimal, but to be absolutely sure, you can suppress all forms of Keyboard/Mouse Input prior to executing the code, then enable Input again post execution. Refer to Code Lines 4 and 32.

P.S. - Error Checking intentionally omitted.
Expand|Select|Wrap|Line Numbers
  1. Public Declare Function BlockInput Lib "USER32.DLL" (ByVal fBlockIt As Long) As Long
Const conPATH_TO_PDF As String = "C:\Stuff\"
Expand|Select|Wrap|Line Numbers
  1. Dim strPathToPDF As String
  2. Dim strReportName As String
  3.  
  4. BlockInput True
  5.  
  6. 'Changes the Default Printer, then resets it to the original Printer.
  7. 'This will only work if the report is set up to Print to the Default
  8. 'Printer
  9. Set Application.Printer = Application.Printers("PDF Converter")
  10.  
  11. 'Report Name assign to Variable
  12. strReportName = "rptPDF"
  13.  
  14. 'Make the *.PDF File Unique
  15. strPathToPDF = conPATH_TO_PDF & strReportName & Format$(Now(), "yyyymmddhhmmss") & ".pdf"
  16.  
  17. DoCmd.OpenReport strReportName, acViewNormal, , , acWindowNormal
  18.  
  19. 'Fill in the Absolute/Unique Path in the Save As Dialog
  20. SendKeys strPathToPDF, True
  21.  
  22. 'TAB to the Save Command Button
  23. SendKeys "{TAB 2}", True
  24.  
  25. 'Simulate Clicking the Save Key
  26. SendKeys "{ENTER}", True
  27. SendKeys "{ENTER}", True
  28.  
  29. 'Reset the Default Printer back to the way it was
  30. Set Application.Printer = Nothing
  31.  
  32. BlockInput False
Jan 17 '10 #7
TheSmileyCoder
2,322 Expert Mod 2GB
Well I use a DB that creates (and formats) 100 page long documents (Requirement specifications), as well as doing crossreferences, image formatting, and creating a Table of contents, so it takes around 30 seconds for my code to generate the report. I imagine sending it to the CutePDF writer is another 5 seconds.

What would happen if an error occured while you have blocked input? Would you be "stuck"?
I presume you could add a error handler like:

Expand|Select|Wrap|Line Numbers
  1. Public Sub GenereatePDF()
  2. On error goto Err_Handler
  3. BlockInput True
  4.  
  5. /Code code code
  6.  
  7.  
  8. Err_Handler:
  9. BlockInput False 
  10.  
Jan 17 '10 #8
ADezii
8,834 Expert 8TB
Hello TheSmileyOne, you answered your own question. Please be advised that this approach is a little 'HAIRY' since it is usually never a good idea to block all forms of Input into an Application. I do feel, however, with careful coding and a little User Training, it can be practical. The decision is all yours.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command13_Click()
  2. On Error GoTo Err_Command13_Click:
  3. Const conPATH_TO_PDF As String = "C:\Stuff\"
  4. Dim strPathToPDF As String
  5. Dim strReportName As String
  6.  
  7. BlockInput True
  8.  
  9. 'Changes the Default Printer, then resets it to the original Printer.
  10. 'This will only work if the report is set up to Print to the Default
  11. 'Printer
  12. Set Application.Printer = Application.Printers("PDF Converter")
  13.  
  14. 'Report Name assign to Variable
  15. strReportName = "rptPDF"
  16.  
  17. 'Make the *.PDF File Unique
  18. strPathToPDF = conPATH_TO_PDF & strReportName & Format$(Now(), "yyyymmddhhmmss") & ".pdf"
  19.  
  20. DoCmd.OpenReport strReportName, acViewNormal, , , acWindowNormal
  21.  
  22. 'Fill in the Absolute/Unique Path in the Save As Dialog
  23. SendKeys strPathToPDF, True
  24.  
  25. 'TAB to the Save Command Button
  26. SendKeys "{TAB 2}", True
  27.  
  28. 'Simulate Clicking the Save Key
  29. SendKeys "{ENTER}", True
  30. SendKeys "{ENTER}", True
  31.  
  32. 'Reset the Default Printer back to the way it was
  33. Set Application.Printer = Nothing
  34.  
  35. BlockInput False
  36.  
  37. Exit_Command13_Click:
  38.   Exit Sub
  39.  
  40. Err_Command13_Click:
  41.   BlockInput False
  42.     MsgBox Err.Description, vbExclamation, "Error in Command13_Click()"
  43.       Resume Exit_Command13_Click
  44. End Sub
Jan 17 '10 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: JSB | last post by:
I am new to Access and am hoping an Access guru out there can help me. I am using an Access 2002 project to report on a SQL Server database. I am reporting various information for around 100...
1
by: Aby Mathews | last post by:
I am using Cutepdf to convert access report to pdf format. It's working fine. How can I set the save location and save as file by default and how can I attach this file automatically to outlook....
2
by: Mike Alpha | last post by:
I'm using Access 2000 and I can't seem to get it to send just one page of a report to Outlook Express. It will only send the whole report. I right click on Send To Mail Recipient and the Send box...
12
by: DGB | last post by:
Hello All, I have been doing quite a bit of research in this group and have not been able to find the answer that I am looking for. Here is my issue. We currently have Adobe Acrobat v5...
13
by: salad | last post by:
Hi Guys: I was stuck. I needed to send a report to a file. My beautiful report(s) in Access were going to require loss of formatting with RTFs, a PITA in WordMailMerge, sending it as a text...
6
by: Bluejay906 | last post by:
I am trying to upgrade a squirrelly Access app. I need to change a button on a form to run VBA instead of a macro, because the user wants to be able to print the report to a printer or to a CutePDF....
8
by: Good Man | last post by:
Hi there We've found a fantastic solution for generating PDF documents with fairly complex XHTML and CSS. It does a great job (on all our tests so far...) It's called "Prince" (...
4
by: mehrhardt | last post by:
My situtation: I'm not very good at Access. I've created a report (R_SalesHist) listing 1000 customers sales history. Each customer's sales history fits nicely on a single page. When I run...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.