File creation wait timer
in [Programming]
|
Prev: Content Size for WebBrowser ActiveX Control
Next: On some PC's prints to default windows printer rather then Acrobat PDF defined in code
From: Ken on 11 Sep 2009 19:49 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 *************************************
From: dksaluki on 11 Sep 2009 20:59 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
From: Charabeuh on 11 Sep 2009 21:05 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" <kolson1971(a)earthlink.net> a �crit dans le message de news:%23C$3BqzMKHA.3412(a)TK2MSFTNGP04.phx.gbl... > 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 > ************************************* >
From: Ken on 12 Sep 2009 03:52 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" <Please(a)FeedBack.fr> wrote in message news:eMgplU0MKHA.4064(a)TK2MSFTNGP06.phx.gbl... > 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" <kolson1971(a)earthlink.net> a �crit dans le message de > news:%23C$3BqzMKHA.3412(a)TK2MSFTNGP04.phx.gbl... >> 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 >> ************************************* >> >
From: Charabeuh on 12 Sep 2009 05:19
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" <kolson1971(a)earthlink.net> a �crit dans le message de news:eg44L43MKHA.1280(a)TK2MSFTNGP04.phx.gbl... > 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" <Please(a)FeedBack.fr> wrote in message > news:eMgplU0MKHA.4064(a)TK2MSFTNGP06.phx.gbl... >> 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" <kolson1971(a)earthlink.net> a �crit dans le message de >> news:%23C$3BqzMKHA.3412(a)TK2MSFTNGP04.phx.gbl... |