Automatically creating pdf through VBA

  • Thread starter Thread starter carls
  • Start date Start date
C

carls

I am having a small issue automatically saving a created
pdf file through VBA. I want to name the pdf file I am
creating based on a varibale from my code(which i do have
working but it only works correctly 50% of the time).
Sometimes the pdf file saves perfectly and other times it
saves as a file name that is missing anywhere from the
first 3-5 characters. How do i get it to name the file
properly every time? Here is my code sample:

DoCmd.SetWarnings False
Dim rs As Recordset
Dim db As Database
Dim filename As String
Dim pdfname As String
Dim keystrokes As String

Set db = CurrentDb
Set rs = db.OpenRecordset("rsname")

rs.MoveFirst
key1 = "{enter}"

With rs
Do While Not .EOF
filename = rs![ProdNum]
pdfname = filename & ".pdf"

DoCmd.OpenReport "rptname", acViewPreview
sendkeys pdfname, False
sendkeys (key1), False
DoCmd.PrintOut
rs.MoveNext
DoCmd.Close acReport, "rptname", acSaveNo
Loop
End With
DoCmd.SetWarnings True
 
Hi Carls,

We may need to use some other methods since sendkey does not work
accurately always; it seems you are trying to print a parametered report,
if the report is based on a query, we can re-create the query with the real
parameter value each time before printing the report. The main steps in the
loop can be:

1. Re-create the underlying query the report is based on with the parameter
value coming from recordset.

2. Print the report.

The logic can be:

With rs
Do While Not .EOF
filename = rs![ProdNum]
pdfname = filename & ".pdf"

Re-create the query

DoCmd.PrintOut
rs.MoveNext
Loop
End With

For

For how to modify the query, please refer to the article:

HOWTO: Use ADO and ADOX to Modify the Base Query of an Access QueryDef
http://support.microsoft.com/?id=255782




Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.


--------------------
| Content-Class: urn:content-classes:message
| From: "carls" <[email protected]>
| X-Tomcat-NG: microsoft.public.access.modulesdaovba
|
| I am having a small issue automatically saving a created
| pdf file through VBA. I want to name the pdf file I am
| creating based on a varibale from my code(which i do have
| working but it only works correctly 50% of the time).
| Sometimes the pdf file saves perfectly and other times it
| saves as a file name that is missing anywhere from the
| first 3-5 characters. How do i get it to name the file
| properly every time? Here is my code sample:
|
| DoCmd.SetWarnings False
| Dim rs As Recordset
| Dim db As Database
| Dim filename As String
| Dim pdfname As String
| Dim keystrokes As String
|
| Set db = CurrentDb
| Set rs = db.OpenRecordset("rsname")
|
| rs.MoveFirst
| key1 = "{enter}"
|
| With rs
| Do While Not .EOF
| filename = rs![ProdNum]
| pdfname = filename & ".pdf"
|
| DoCmd.OpenReport "rptname", acViewPreview
| sendkeys pdfname, False
| sendkeys (key1), False
| DoCmd.PrintOut
| rs.MoveNext
| DoCmd.Close acReport, "rptname", acSaveNo
| Loop
| End With
| DoCmd.SetWarnings True
|
|
|
 
I am not using a parameterized query for the report. the
report works just fine the problem is when I try to save
the report. I just am using the parameter to name the pdf
file. when the box pops up to save the pdf file sometimes
the send keys works and other times it doesn't. It is
kinda like the pdf writer hangs sometimes and causing the
send keys to be sent too early.
 
I am using the pdf writer to create the file all i want
to do is not show the dialog box that pops up on my
screen. This can't be that hard...can it? I just wan to
taccept the defaults because i have written a batch file
to trename the file after it is created.
-----Original Message-----
What software are you using to create PDF files?

Adobe Acrobat uses the registry (HKEY_CURRENT_USER\Software\Adobe\Acrobat
PDFWriter) and www.pdf995.com uses pdf995.ini to store its settings.

Search www.google.com/groups

HTH

Jose

carls said:
I am having a small issue automatically saving a created
pdf file through VBA. I want to name the pdf file I am
creating based on a varibale from my code(which i do have
working but it only works correctly 50% of the time).
Sometimes the pdf file saves perfectly and other times it
saves as a file name that is missing anywhere from the
first 3-5 characters. How do i get it to name the file
properly every time? Here is my code sample:

DoCmd.SetWarnings False
Dim rs As Recordset
Dim db As Database
Dim filename As String
Dim pdfname As String
Dim keystrokes As String

Set db = CurrentDb
Set rs = db.OpenRecordset("rsname")

rs.MoveFirst
key1 = "{enter}"

With rs
Do While Not .EOF
filename = rs![ProdNum]
pdfname = filename & ".pdf"

DoCmd.OpenReport "rptname", acViewPreview
sendkeys pdfname, False
sendkeys (key1), False
DoCmd.PrintOut
rs.MoveNext
DoCmd.Close acReport, "rptname", acSaveNo
Loop
End With
DoCmd.SetWarnings True
Click to expand...


.
Click to expand...
 
Hi Carls,

You can try the method in the article below:

Save a report's output as a PDF file*
http://www.mvps.org/access/reports/rpt0011.htm

This response contains a reference to a third-party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.



Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.




--------------------
| Content-Class: urn:content-classes:message
| From: "carls" <[email protected]>
| Sender: "carls" <[email protected]>
| X-Tomcat-NG: microsoft.public.access.modulesdaovba
|
| I am not using a parameterized query for the report. the
| report works just fine the problem is when I try to save
| the report. I just am using the parameter to name the pdf
| file. when the box pops up to save the pdf file sometimes
| the send keys works and other times it doesn't. It is
| kinda like the pdf writer hangs sometimes and causing the
| send keys to be sent too early.
| >|
|
| >|
| >|
| >
| >.
| >
|
 
Hi Carls,

I notice you start a new thread titled "Hiding a dialog box while creating
a pdf" in this newsgroup, for your convenience, I list my response below in
this thread:


Carls, I don't find an easy method so far, however, I believe calling
Windows API can be the "safe" method to perform this task, we can send the
filename to the dialog window by Windows API; below is a sample to get
start, this sample send string "hello" to notepad window.

Option Compare Database
Option Explicit

Private Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" _
(ByVal hWnd As Long, ByVal Msg As Long, wParam As Any, lParam As Any) As
Long

Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWndParent As Long, ByVal hwndChildAfter As Long, ByVal lpszClass As
_
String, ByVal lpszWindow As String) As Long

Private Const WM_SETTEXT = 12
Private Sub Command0_Click()

ToNotePad "hello"

End Sub

Public Sub ToNotePad(x As String)
'assuming notepad is open
Dim h As Long, ed As Long, t As Long

h = FindWindow("Notepad", "Untitled - Notepad")
If (h <> 0) Then
ed = FindWindowEx(h, 0, "Edit", "")
If (ed <> 0) Then
SendMessage ed, WM_SETTEXT, ByVal 0, ByVal x
Else
MsgBox "Could not find notepad's edit"
End If
Else
MsgBox "Could not find notepad"
End If
End Sub

Please feel free to reply to the thread if you have any questions or
concerns.




Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
| Content-Class: urn:content-classes:message
| From: "carls" <[email protected]>
| NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
| X-Tomcat-NG: microsoft.public.access.modulesdaovba
|
| I am not using a parameterized query for the report. the
| report works just fine the problem is when I try to save
| the report. I just am using the parameter to name the pdf
| file. when the box pops up to save the pdf file sometimes
| the send keys works and other times it doesn't. It is
| kinda like the pdf writer hangs sometimes and causing the
| send keys to be sent too early.
| >|
|
| >|
| >|
| >
| >.
| >
|
 
Back
Top