File creation wait timer

[Ken]

I'm getting a File Doesn't Exist error when I try to Call this function from
within my Email function. Sometimes I'm sure it is a timing issue where the
email is trying to attach this file before it is finished being created.

Would some one be kind enough to supply me with some timer code that tests
and waits for the file to be created before continuing?

Thanks,
Ken

**************************************
Public Function PrintToPDF()

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

'NEED TIMER HERE I THINK

'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."

End Function
*************************************

 

[dksaluki]

You might look into Application.Wait method. go to your VBE
editor, just type "wait" somewhere in code, then out your cursor
inside the word "wait" and hit F1


hope that helps or gives ideas,

dk

 

[Charabeuh]

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]

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.

 

[Charabeuh]

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]

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
------------------------------------------

 

[Charabeuh]

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
'------------------------------------------------------------------------------------------

 

[Charabeuh]

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,

 

[Ken]

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.

 

[Charabeuh]

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]

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.

 

[Charabeuh]

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]

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

 

[Charabeuh]

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]

After trying that I'm getting another error. See this screen video link:
http://www.genesisresource.com/ken/newsgroups/WaitFileTimeError 1.wmv

 

[Chip Pearson]

I have module named modWait.bas that has some functions that you may
find useful. You can download a zip file containing this module from
http://www.cpearson.com/Zips/modWait.zip . Unzip the file to some
folder, open VBA, go to the File menu, choose Import File, navigate to
the folder in which you unzipped the file, and choose modWait.bas.
This will create a new module in your project named modWait.

The functions you might want to try are:

---------------------------
WaitForFileCreate
---------------------------
This waits for a specified file to be created. The declaration is:

Public Function WaitForFileCreate(WaitFileName As String, _
TimeOutSeconds As Long, _
Optional BreakKey As BreakKeyHandler = BreakKeyHandler.Ignore, _
Optional SleepMilliseconds As Long = 500) As FileWaitStatus

where WaitFileName is the name of the file to wait upon,
TimeOutSeconds is the number of seconds to wait before abandoning the
wait. For an infinite wait, set TimeOutSeconds to 0. BreakKey
indicates how the function should respond if the user hits CTRL BREAK.
You can set it to ignore the break key, terminate the wait, or prompt
the user whether to continue the wait. SleepMilliseconds is the
number of milliseconds to pause before retesting the file. If the file
already exists, it returns immediately with a result of Success.

The function returns:

Public Enum FileWaitStatus
Success = -1
UserBreak = 1
FileNotFound
WaitTimeout
End Enum

Success = the wait was successful and the file was created.
UserBreak = the user hit CTRL BREAK to break out of the wait.
WaitTimeout = the TimeOutSeconds period expired before the file was
created.

---------------------------
WaitForFileClose
---------------------------
This waits for a specified file to be closed. The declaration is

Public Function WaitForFileClose(WaitFileName As String, _
TimeOutSeconds As Long, _
Optional BreakKey As BreakKeyHandler = BreakKeyHandler.Ignore, _
Optional SleepMilliseconds As Long = 500) As FileWaitStatus

The parameters have the same meaning in this procedure as they do in
WaitForFileCreate. If the file does not exist, the function returns
immediately with a result of FileNotFound. If the file is not open,
the function return immediately with a result of Success.

The function returns

Public Enum FileWaitStatus
Success = -1
UserBreak = 1
FileNotFound
WaitTimeout
End Enum

Success = the file was closed successfully or was not open.
UserBreak = the user hit CTRL BREAK to break out of the wait.
FileNotFound = the file was not found.
WaitTimeout = the TimeOutSeconds period expired before the file was
closed.

---------------------------
ShellAndWait
---------------------------
This calls Shell to execute a program or command line and waits for
the Shell'd program to finish. The declaration is:

Public Function ShellAndWait(ShellCommand As String, _
TimeOutMs As Long, _
ShellWindowState As VbAppWinStyle, _
BreakKey As ActionOnBreak) As ShellAndWaitResult

where ShellCommand is the command to be passed to Shell, TimeOutMs is
the number of milliseconds to wait before abandoning the wait,
ShellWindowState is the window state to pass to the Shell function,
and BreakKey indicates how to handle the Break key.

The function returns

Public Enum ShellAndWaitResult
Success = 0
Failure
TimeOut
InvalidParameter
SysWaitAbandoned
UserWaitAbandoned
UserBreak
End Enum

Success = The shell'd program ended normally.
Failure = A system error occurred
TimeOut = The timeout period expired before the program finished.
InvalidParameter = The command passed to Shell was invalid.
SysWaitAbandoned = The system abandoned the wait.
UserWaitAbandoned = The user abandoned the wait.
UserBreak = The user pressed CTRL ESC to break out of the wait.

See also http://www.cpearson.com/excel/ShellAndWait.aspx .

In addition to these functions, you might also want to take a look at
Excel's OnTime method and at using Windows system timers. See
http://www.cpearson.com/excel/OnTime.aspx for a discussion and
examples of OnTime and the Windows Timer API functions.



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Ken]

Here is the correct link.
http://www.genesisresource.com/ken/newsgroups/WaitFileTimeError-1.wmv

 

[Charabeuh]

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]

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]

In the last code replace:
doevents
with
MoreTime = MoreTime

to verify Doevents does not interfere...

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. ] %%

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