Delay code execution

[easycode]

Hi all

i am working in an application where access have to stop for a short delay after another utility program is finished before it continue working. I have checked several pages over the internet icluding tek-tips where some users suggest to use "WaitForSingleObject" but this option apparently works only with the Shell command, means that it will work only when access opens a D.O.S application and wait until it is finished and then the control is passed to access. I need to have the same effect with a Windows application, i am opening excel and doing some calculations and the save and exit but i need access to delay until excel is done.

Thanks in advanced for any suggestions.

 

[PHV]

i am opening excel and doing some calculations and the save and exit
If you do that with OLE Automation in a modal form then your goal should be achieved.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[easycode]

Thanks for replying PHV

Yes you are right but there is a time when saving the document that system is waiting for users reponse and while doing that access continues processing and release an error message because the further instructions are based on that spreadsheet.

Hope this explains my problem

 

[PHV]

system is waiting for users reponse
Asking for what ?
Why not post the relevant part of your code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[easycode]

Ok Here is the code

Sub Processdemand(fpath As String, ffilename As String)
On Error GoTo handleerror


Dim objXLApp As Excel.Application
Dim objXLdata As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLWBook As Excel.Workbook

Set objXLBook = GetObject(ffilename)
Set objXLApp = objXLBook.Parent

objXLApp.Visible = True
objXLBook.Windows(1).Visible = True

objXLBook.Worksheets("data").Select
objXLBook.Worksheets("data").Copy
objXLApp.ActiveWorkbook.SaveAs Filename:=fpath & "\Mydemand.xls" 'User saves as mydemand
objXLApp.Quit

MsgBox "After Saving changes on excel, Press any key to continue"
Set objXLWBook = GetObject(fpath + "\mydemand.xls")

Set objXLdata = objXLWBook.Parent
objXLdata.Visible = True
objXLdata.Windows(1).Visible = True
objXLdata.Range("A1").Select
Do While Len(objXLdata.ActiveCell.FormulaR1C1) <> 0
If InStr(objXLdata.Selection.NumberFormat, "mmm-yy") Then
Call assignmonth(objXLdata)
Else
'MsgBox "This cell is not in a date format"
End If
objXLdata.ActiveCell.Offset(0, 1).Select
Loop

objXLdata.ActiveWorkbook.Save
objXLdata.Quit

End Sub

I am using that MsgBox "After Saving changes on excel, Press any key to continue" as a temporary solution because it works it gives time to the user to answer the questions in excel "Do you want to save your changes?" then user respond yes or not and then the control returns to access.

 

[easycode]

can you show the way to put the code in a frame

---------------------------------------------------------
code
---------------------------------------------------------
i see some people put it in this way so there is got to be an easy way to do this.

Thanks

 

[IamCornholio]

Try this on for size... Copy this code:

[blue]
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)[/blue]

and past it under the first line "Option Compare Database" in your General Declarations section of the module where you're going to run the code.

Later you just call it by using
[blue]Sleep 30000[/blue]

which tells it to sleep for 30000 miliseconds, i.e. 30 seconds. I use this to check for data, if there is no data, check in 30 seconds, until a certain amount of time has passed, or, it finds the data. Works great...

 

[easycode]

Thanks Cornholio

Have this happend to you: "when you are manually saving a worksheet, the same worksheet sometimes takes 2 seconds sometimes 30 or even 2 minutes" if excel takes 5 seconds that means that the user has to wait 25 seconds until the sleep returns the control to access and what if takes 32 secondes that means that i will get an error message because the sleep returned the control to access and it continues executing the instructions but excel did not finish saving yet. So i am just trying to find another way, but thank you much for your response. appreciate it.

 

[IamCornholio]

I would just tell it to wait at least a couple minutes.. Could be network congestion?

I would have the sleep cycle wait for a couple minutes and call it a day..

 

[Eupher]

Hi, easycode,

First, to answer your question about showing code in a special block in TT posts: check the box below the window in which you post messages that says "Process TGML". You may click on that link to see what TGML codes are recognized and how to use them. To display code, enclose your code like so [&#91;]code][blue]your code goes here...[/blue][&#91;]/code]
...which will be displayed as:

[blue]your code goes here...[/blue]

I suspect PHV may have a better solution than I to your main problem (he almost always does!) - but here is what I have done. I have set up a routine that checks for the title bar of all open windows (should include dialog boxes!) - and as long as the title bar in question is found - keep looping through the routine! So you are not actually pausing code - code is continuing to run in a loop until the open dialog condition no longer exists. Here's my code, paste it into a new module and customize as you wish:

Option Compare Database
Option Explicit

Declare Function EnumWindows Lib "user32.dll" (ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long
Declare Function GetWindowTextLength Lib "user32.dll" Alias "GetWindowTextLengthA" _
(ByVal hWnd As Long) As Long
Declare Function GetWindowText Lib "user32.dll" Alias "GetWindowTextA" _
(ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Declare Function GetActiveWindow Lib "user32.dll" () As Long
Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" _
(ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Public Const WM_CLOSE = &H10

Public ghWnd As Long
Public gCurrenthWnd As Long
Public SynQFound As Boolean
[green]'------------------------------[/green]
Public Function EnumWindowsProc(ByVal hWnd As Long, ByVal lParam As Long) As Long
On Error Resume Next
Dim slength As Long, TitleBar As String
Dim retval As Long
Static winnum As Integer
SynQFound = False
  winnum = winnum + 1
  slength = GetWindowTextLength(hWnd) + 1
  If slength > 1 Then
    TitleBar = Space(slength)
    retval = GetWindowText(hWnd, TitleBar, slength)
    If InStr(TitleBar, "SynQ") Then
        SynQFound = True
        ghWnd = hWnd
        Exit Function
        ElseIf InStr(TitleBar, "Synchronization complete") Then
            SynQFound = True
            ghWnd = hWnd
            Exit Function
    End If
  End If
  EnumWindowsProc = 1
End Function
[green]'------------------------------[/green]
Public Function WaitForSynQ()
SynQFound = True
Do While SynQFound
    Call EnumWindows(AddressOf EnumWindowsProc, 0)
    DoEvents
Loop
End Function

I haven't commented this code at all; let me know if you need some help understanding what's going on in these functions.

HTH,

Ken S.

 

[easycode]

Thanks to all for replying
Hi Eupher

i like this idea of checking the title of the windows until access dont find the title in question.

I will need a little help to setup this routine correctly

I believe that the main function to call is WaitForSynQ, but i dont see any parameter, how does the system knows what windows is it looking for?

Thanks again Eupher, and i agree on your comments about PHV, he had helped me a lot.

 

[Eupher]

Hi, easycode,

You are correct, WaitForSynQ is the procedure you call - in my app, I was invoking an Outlook plugin called SynQ from Outlook's menu and I needed the code to wait until SynQ finished (including a dialog box prompt from SynQ) before handing control back to Access. But you should be able to use it for any running process, assuming it has a Windows handle and a title bar. Here is the part that where the expected window title is identified:

    If InStr(TitleBar, "[blue]SynQ[/blue]") Then
        SynQFound = True
        ghWnd = hWnd
        Exit Function
        ElseIf InStr(TitleBar, "[blue]Synchronization complete[/blue]") Then
            SynQFound = True
            ghWnd = hWnd
            Exit Function
    End If

The code simply looks for the text string in the window's title bar.

HTH,

Ken S.

p.s. there are other ways to enumerate running processes, not just by identifying the title bar string... but this way works for my app...

 

[PHV]

What about this ?
...
objXLBook.Worksheets("data").Select
objXLBook.Worksheets("data").Copy
Set objXLWBook = objXLApp.ActiveWorkbook
objXLBook.Close True
objXLWBook.SaveAs Filename:=fpath & "\Mydemand.xls" 'User saves as mydemand
...

BTW, why quit excel (objXLApp) to just spawn it again (objXLdata) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Eupher]

Sheesh, sometimes I'm dumb...

No need for the ElseIf, this is better:

    If InStr(TitleBar, "SynQ") Or InStr(TitleBar, "Synchronization complete") Then
        SynQFound = True
        ghWnd = hWnd
        Exit Function
    End If

Of course, you probably don't need the Or clause at all...

Ken S.

 

[easycode]

Eupher
i am getting an error message in the hWnd, variable not define, what type of data is hWnd

Sub mycontrol(TitleBar)
    If InStr(TitleBar, "SynQ") Then
        SynQFound = True
        ghWnd = hWnd     'HERE STOPS AND DISPLAY THE ERROR
        Exit Sub
    End If
End Sub

PHV Thanks for replying
i think i need the objXLApp.Quit because otherwise it will have Excel Open and i just need it to save and close it i dont know if there is a property ojXLApp.Close, but i will try it to see how it works.


Thanks to both!

 

[Eupher]

Okay, a couple of things:

I posted an example of code I used in MY app. My app was looking for "SynQ" in the title bar - yours is not! You must replace "SynQ" with the string that corresponds to the title bar of the app YOU are waiting for.

hWnd is an argument of the EnumWindowsProc function. It looks like you have cut and pasted a small snippet of the function into your own Sub. That will not work! You need to use ALL of it - the declarations, the constants, and both the entire EnumWindowsProc and WaitForSynQ functions. The ONLY customization you need should be the literal title bar string(s). Of course, you can rename WaitForSynQ to something that is meaningful for your app, for instance, WaitForExcel.

HTH,

Ken S.

 

[MichaelRed]

... why oh why ... do "we" resort to the arcane and end up searching for an obscure workaround to resolve the ambiguities of the originally arcane process?

Usually, i think, because we always have time to do it over (or research the obscure to ge the arcane to do what we thoughtr was easier in hte arcane box) but not the time to do it right the first time?

Here, I cannot really think that what ever is being done in Excel to begin with cannot reasonably be done directly in Ms. A. ... thus simplifying the origiinal to eliminate the arcane and avoid the obscure.

Without any real review, the original 'process' seems to just get some dates from an excel sheet. somewhere through the manipulation, (obviously?) there is some manipulation (e.g. edit) of the spreadsheet ... so why not just import the (*&^(&*^%(&*^ spreadsheet into a temp table, present this in an Access format (form, table, query ... ) let the user manipulate it and when done, just cntinue? I SUSPECT that the spreadsheet must include some formulas (calculations) which affect the information used in the subsquent (Ms. A. App) processing -- but it seems likr these could generally be done in Ms. A. ??????????????????????????



MichaelRed

 

[PHV]

easycode, you didn't reply to my post stamped 21 Feb 07 15:34.
I persist to think that no API call is necessary ...

 

[Eupher]

I defer to PHV and MichaelRed - I posted my code because I have no experience manipulating the Excel object in VBA, so couldn't help in that regard...

Ken S.

 

[easycode]

thanks to all who replyied

PHV i did reply to your post, please read message Nº 15 it's nex to the reply to Eupher in the same message.

Thanks to all, again