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.
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. - Const conPATH_TO_PDF As String = "C:\Stuff\"
-
Dim strPathToPDF As String
-
Dim strReportName As String
-
-
'Changes the Default Printer, then resets it to the original Printer.
-
'This will only work if the report is set up to Print to the Default
-
'Printer
-
Set Application.Printer = Application.Printers("PDF Converter")
-
-
'Report Name assign to Variable
-
strReportName = "rptPDF"
-
-
'Make the *.PDF File Unique
-
strPathToPDF = conPATH_TO_PDF & strReportName & Format$(Now(), "yyyymmddhhmmss") & ".pdf"
-
-
DoCmd.OpenReport strReportName, acViewNormal, , , acWindowNormal
-
-
'Fill in the Absolute/Unique Path in the Save As Dialog
-
SendKeys strPathToPDF, True
-
-
'TAB to the Save Command Button
-
SendKeys "{TAB 2}", True
-
-
'Simulate Clicking the Save Key
-
SendKeys "{ENTER}", True
-
SendKeys "{ENTER}", True
-
-
'Reset the Default Printer back to the way it was
-
Set Application.Printer = Nothing
8 16261
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)
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. - Const conPATH_TO_PDF As String = "C:\Stuff\"
-
Dim strPathToPDF As String
-
Dim strReportName As String
-
-
'Changes the Default Printer, then resets it to the original Printer.
-
'This will only work if the report is set up to Print to the Default
-
'Printer
-
Set Application.Printer = Application.Printers("PDF Converter")
-
-
'Report Name assign to Variable
-
strReportName = "rptPDF"
-
-
'Make the *.PDF File Unique
-
strPathToPDF = conPATH_TO_PDF & strReportName & Format$(Now(), "yyyymmddhhmmss") & ".pdf"
-
-
DoCmd.OpenReport strReportName, acViewNormal, , , acWindowNormal
-
-
'Fill in the Absolute/Unique Path in the Save As Dialog
-
SendKeys strPathToPDF, True
-
-
'TAB to the Save Command Button
-
SendKeys "{TAB 2}", True
-
-
'Simulate Clicking the Save Key
-
SendKeys "{ENTER}", True
-
SendKeys "{ENTER}", True
-
-
'Reset the Default Printer back to the way it was
-
Set Application.Printer = Nothing
NICE!
Simple, but impressive. Looking forward to testing it.
It was actually functional on my end, I guess anything is possible! (LOL)
P.S. - The strange part is that the first
appears to be ignored.
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)
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. - Public Declare Function BlockInput Lib "USER32.DLL" (ByVal fBlockIt As Long) As Long
Const conPATH_TO_PDF As String = "C:\Stuff\" - Dim strPathToPDF As String
-
Dim strReportName As String
-
-
BlockInput True
-
-
'Changes the Default Printer, then resets it to the original Printer.
-
'This will only work if the report is set up to Print to the Default
-
'Printer
-
Set Application.Printer = Application.Printers("PDF Converter")
-
-
'Report Name assign to Variable
-
strReportName = "rptPDF"
-
-
'Make the *.PDF File Unique
-
strPathToPDF = conPATH_TO_PDF & strReportName & Format$(Now(), "yyyymmddhhmmss") & ".pdf"
-
-
DoCmd.OpenReport strReportName, acViewNormal, , , acWindowNormal
-
-
'Fill in the Absolute/Unique Path in the Save As Dialog
-
SendKeys strPathToPDF, True
-
-
'TAB to the Save Command Button
-
SendKeys "{TAB 2}", True
-
-
'Simulate Clicking the Save Key
-
SendKeys "{ENTER}", True
-
SendKeys "{ENTER}", True
-
-
'Reset the Default Printer back to the way it was
-
Set Application.Printer = Nothing
-
-
BlockInput False
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: -
Public Sub GenereatePDF()
-
On error goto Err_Handler
-
BlockInput True
-
-
/Code code code
-
-
-
Err_Handler:
-
BlockInput False
-
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. - Private Sub Command13_Click()
-
On Error GoTo Err_Command13_Click:
-
Const conPATH_TO_PDF As String = "C:\Stuff\"
-
Dim strPathToPDF As String
-
Dim strReportName As String
-
-
BlockInput True
-
-
'Changes the Default Printer, then resets it to the original Printer.
-
'This will only work if the report is set up to Print to the Default
-
'Printer
-
Set Application.Printer = Application.Printers("PDF Converter")
-
-
'Report Name assign to Variable
-
strReportName = "rptPDF"
-
-
'Make the *.PDF File Unique
-
strPathToPDF = conPATH_TO_PDF & strReportName & Format$(Now(), "yyyymmddhhmmss") & ".pdf"
-
-
DoCmd.OpenReport strReportName, acViewNormal, , , acWindowNormal
-
-
'Fill in the Absolute/Unique Path in the Save As Dialog
-
SendKeys strPathToPDF, True
-
-
'TAB to the Save Command Button
-
SendKeys "{TAB 2}", True
-
-
'Simulate Clicking the Save Key
-
SendKeys "{ENTER}", True
-
SendKeys "{ENTER}", True
-
-
'Reset the Default Printer back to the way it was
-
Set Application.Printer = Nothing
-
-
BlockInput False
-
-
Exit_Command13_Click:
-
Exit Sub
-
-
Err_Command13_Click:
-
BlockInput False
-
MsgBox Err.Description, vbExclamation, "Error in Command13_Click()"
-
Resume Exit_Command13_Click
-
End Sub
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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....
|
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...
|
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...
|
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...
|
by: bplim88 |
last post by:
|
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....
|
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" (...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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...
| |