K
Ken
Same problem, but Distiller window is open a long time, as it used to appear
and close in a flash.
and close in a flash.
Charabeuh said:In the last code replace:
doevents
with
MoreTime = MoreTime
to verify Doevents does not interfere...
Ken said:Well that eliminate the error, but now the pdf is not being created.
Here is what the PigeonTrainingCalendar.LOG file says:
%%[ Error: undefined; OffendingCommand: E ]%%
%%[ Flushing: rest of job (to end-of-file) will be ignored ]%%
%%[ Warning: PostScript error. No PDF file produced. ] %%
Charabeuh said:The error message seems to deal with access to a file.
we could replace the waiting routine by just waiting for seconds and not
looking for the file.
After the line testing the ReturnValue of Distiller, replace the line:
WaitFileTime PDFFileName,5
directly with the 5 lines:
Dim MoreTime
MoreTime = Timer + 5
Do Until Timer > MoreTime
DoEvents
Loop
"Ken" <[email protected]> a écrit dans le message de
Here is the correct link.
http://www.genesisresource.com/ken/newsgroups/WaitFileTimeError-1.wmv
After trying that I'm getting another error. See this screen video
link:
http://www.genesisresource.com/ken/newsgroups/WaitFileTimeError 1.wmv
So we can do two things
1) the input file of Distiller should be entirely build before
Distiller couls use it.
Creating the first file (.ps file I guess) :
You could wait (for exemple 4 seconds or less or more) to be sure
that this process of building
the .ps file is totally accomplished.
to do so, insert the following line:
WaitFileTime(PSFileName, 4)
after the line:
ActiveSheet.PrintOut PrintToFile:=True, PrToFileName:=PSFileName
2) After having launched Distiller, we should give time to Distiller
to create the PDF file before be able to use it in your email sub.
To do so:
After the line:
If ReturnValue = 0 Then MsgBox "Creation of " & PDFFileName &
"failed."
you insert the code to wait for the creation of the .pdf file:
WaitFileTime(PDFFileName , 5)
if you wish to wait five seconds.
"Ken" <[email protected]> a écrit dans le message de
Looks like I got confused somewhere along the way.
The PSFileName was being created all along. The the issue is that
PDFFileName is the one that is not being created.
I did both your tests anyhow and they both created the PSFileName.
Then I tried using both these lines and it WORKS!
ActiveSheet.PrintOut PrintToFile:=True, PrToFileName:=PSFileName
ActiveSheet.PrintOut PrintToFile:=True, PrToFileName:=PDFFileName
The only issue I see now is that the timer is taking place before
Distiller
is doing its thing. Specifically the dialog window shown in the link
below now waits 5
seconds before it is displayed. What is actually needed is to wait 5
seconds
after the distiller displays, as I'm assuming that Distiller process
is why
I need a delay in the first place.
http://www.genesisresource.com/ken/Distiller.bmp
We're getting close!
Ken
hello !
We can try to see if the output file is created without the code to
wait
for
and without the code to create the pdf file.
To do this:
just after the line
ActiveSheet.PrintOut PrintToFile:=True, PrToFileName:=PSFileName
put the instruction
exit sub
Normally, the output file should be created.
Search the output file on your disk with windows explorer
if the file does not exist, we should return to the basics.
Create a new module
insert a new sub TEST
'-------------------
Sub test_1()
If Dir("c:\toto") <> "" Then Kill ("c:\toto")
ActiveSheet.PrintOut PrintToFile:=True, PrToFileName:="c:\toto"
End Sub
'--------------------
then verify that c:\toto exists.
"Ken" <[email protected]> a écrit dans le message de
I tired your first test of creating the file in explorer, and the
code
then
worked. So now that we know the file isn't being created, do you
have an
idea how to fix?
Actually, I'm pretty impressed with your english as the only give
away
was
in your ActiveSheet.PrintOut example.
Timer is a VBA function that returns the number of second elapsed
since
midnight.
If the code gets stuck in the first loop, it can mean:
1) The output file is not created
==> verify that the file is created in windows explorer.
2) The file that is created has another name than PSFileName
3) a third reason that I don't see !
What can we do to verify:
1) while the code is stucked in the first loop (step by step
through
with
F8)
create manually the PSFileName in windows explorer
(right click in the correct folder, new text file -
rename the new file PSFileName)
return to excel vba and continue with F8.
(Since the file is now created, the code should leave the first
loop)
2) or create a new module and insert the code below
then run test.
'---------------------------------------------------------------------
Sub WaitFileTime(xMyFileName As String, xSeconds As Integer)
Dim MoreTime
Do Until Dir(xMyFileName) <> "": DoEvents: Loop
MoreTime = Timer + xSeconds
Do Until Timer > MoreTime: DoEvents: Loop
End Sub
Sub Test()
Dim MyFile As String
MyFile = "c:\testFile.xxx"
If Dir(MyFile) <> "" Then Kill (MyFile)
ActiveSheet.PrintOut PrintToFile:=True, PrToFileName:=MyFile
WaitFileTime MyFile, 5
MsgBox Dir(MyFile) & " exists"
End Sub
'---------------------------------------------------------------------
I apologize for my english...
"Ken" <[email protected]> a écrit dans le message de
When I step through it (F8) I see that the code gets stuck in
the first
DoUntil Loop and never gets to MoreTime Loop.
Are we setting the timer for seconds or minutes?
What is "Timer" as I don't see it declared anywhere? Could that
be part
of the problem.
Just an error of automatic correction in my french excel !
instead of reading
ActiveSheet.Pinot Pintoille:=True, PrToFileName:=PSFileName
one should read :
ActiveSheet.PrintOut PrintToFile:=True,
PrToFileName:=PSFileName
sorry,
"Charabeuh" <[email protected]> a écrit dans le message de
Hello,
It looks like the sendkeys instruction doesn't work anymore
with the new code. Let us drop the sendkeys instruction.
Try this:
replace:
'------------------------------------------------------------------------------------------
'The Sendkeys characters are the full path and filename,
followed by
the
"Enter" key.
' These are buffered until the "print to file" screen appears:
SendKeys PSFileName & "{ENTER}", False
'Print the document to PDF
ActiveSheet.PrintOut , PrintToFile:=True
'------------------------------------------------------------------------------------------
with
'------------------------------------------------------------------------------------------
'Print the document to PDF
ActiveSheet.Pinot Pintoille:=True, PrToFileName:=PSFileName
'------------------------------------------------------------------------------------------
"Ken" <[email protected]> a écrit dans le message de
Thank you again.
Not sure what is happening as now I'm being prompted for
"Output
File
Name", right after ActiveSheet.PrintOut, where as before that
never
happened and the file was created.
Here is what I have
-------------------------------------------
Public Function PrintToPDF()
On Error GoTo FuncErr
Dim PSFileName As String
Dim PDFFileName As String
Dim DistillerCall As String
Dim ReturnValue As Variant
Application.StatusBar = "Creating PDF of Calendar"
' Set folder path and file names
Dim DocsFolder As String
DocsFolder =
CreateObject("WScript.Shell").SpecialFolders("MyDocuments")
PSFileName = DocsFolder & "\PigeonTrainingCalendar.PS"
PDFFileName = DocsFolder & "\PigeonTrainingCalendar.PDF"
'If the files already exist, delete them:
If Dir(PSFileName) <> "" Then Kill (PSFileName)
If Dir(PDFFileName) <> "" Then Kill (PDFFileName)
'The Sendkeys characters are the full path and filename,
followed by
the "Enter" key.
' These are buffered until the "print to file" screen
appears:
SendKeys PSFileName & "{ENTER}", False
'Print the document to PDF
ActiveSheet.PrintOut , PrintToFile:=True
' Wait for PDF to finish being created
WaitFileTime PDFFileName, 5
'Add double quotes around the PS filename and PDF filename:
PSFileName = Chr(34) & PSFileName & Chr(34)
PDFFileName = Chr(34) & PDFFileName & Chr(34)
DistillerCall = "C:\Program Files\Adobe\Acrobat
8\Acrobat\Acrodist.exe" & _
" /n /q /o" & PDFFileName & " " & PSFileName
'Call the Acrobat Distiller to distill the PS file.
ReturnValue is
zero
'if the application doesn't open correctly:
ReturnValue = Shell(DistillerCall, vbNormalFocus)
If ReturnValue = 0 Then MsgBox "Creation of " & PDFFileName &
"failed."
FuncExit:
Exit Function
FuncErr:
MsgBox "An Error occured during email setup or
submission:" &
vbCrLf & Error, vbInformation, "Problem"
Resume FuncExit
End Function
Function WaitFileTime(xMyFileName As String, xSeconds As
Integer)
Dim MoreTime
Do Until Dir(xMyFileName) <> ""
DoEvents
Loop
MoreTime = Timer + xSeconds
Do Until Timer > MoreTime
DoEvents
Loop
End Function
------------------------------------------
Hello,
You could create a new sub and then call the sub where you
want to
wait.
'------------------------------------------------------------------------------------
Sub WaitFileTime(xMyFileName As String, xSeconds As Integer)
Dim MoreTime
Do Until Dir(xMyFileName) <> "": DoEvents: Loop
MoreTime = Timer + xSeconds
Do Until Timer > MoreTime: DoEvents: Loop
End Sub
'------------------------------------------------------------------------------------
then in your code where you want to wait:
'------------------------------------------------------------------------------------
WaitFileTime MyFileName, 5
'------------------------------------------------------------------------------------
"Ken" <[email protected]> a écrit dans le message de
Thank you, but how do I implement it within the existing
code. I
copied and pasted it and changed the MyFileName variable,
but it
seems like my code stops somewhere in the timer code.
Hello,
If you are waiting for the creation of MyFileName
(replace MyFileName with PDFFileName or PSFileName)
since I'm not sure for which file you want to wait.
'-----------------------------------------------------
Dim MoreTime
Do Until Dir(MyFileName) <> ""
DoEvents
Loop
'Perhaps you will need more time to
'wait to the end of creation of the file
'for exemple 5 seconds
MoreTime = Timer + 5
Do Until Timer > MoreTime
DoEvents
Loop
'----------------------------------------------------------
"Ken" <[email protected]> a écrit dans le message
de