I have this code below, which runs a database query according to the the requested ontime. I put the MsgBox in so I could see that the query had been run, but the "Query completed!" message appears the moment the query starts rather than on completion. Any ideas how I can make the message only appear when the query has completed?

Sub StartTime()
Dim Message, Title, Default, MyValue
Message = "Enter the time you would like the query to run."
Title = "When to run the query"
Default = "08:00:00"
MyValue = InputBox(Message, Title, Default)
Application.OnTime TimeValue(MyValue), "DatabaseQuery"
MsgBox "Query completed!"
End Sub

Try placing the...

MsgBox "Query completed!"

in your routine called "DatabaseQuery"

____________________________
Eggheads unite! You have nothing to lose but your yolks.

The routine called "DatabaseQuery" runs an app called BrioQuery.  When I run the brio code independently and then the excel code independently everything works fine. Put them together and it stalls, I have had it happen before. Can I force the macro to wait until the brio app has finished shutting down and then move on to the next bit of code?
Thanks

are you using Shell to start the other app?

here's a snippet for "Shell and Wait"

Private Declare Function OpenProcess Lib "kernel32" _ (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _ ByVal dwProcessId As Long) As Long Private Declare Function GetExitCodeProcess Lib "kernel32" _ (ByVal hProcess As Long, lpExitCode As Long) As Long Private Const STATUS_PENDING = &H103& Private Const PROCESS_QUERY_INFORMATION = &H400 Public Function ShellandWait(ExeFullPath As String, _ Optional TimeOutValue As Long = 0) As Boolean          Dim lInst As Long     Dim lStart As Long     Dim lTimeToQuit As Long     Dim sExeName As String     Dim lProcessId As Long     Dim lExitCode As Long     Dim bPastMidnight As Boolean          On Error GoTo ErrorHandler     lStart = CLng(Timer)     sExeName = ExeFullPath     'Deal with timeout being reset at Midnight     If TimeOutValue > 0 Then         If lStart + TimeOutValue < 86400 Then             lTimeToQuit = lStart + TimeOutValue         Else             lTimeToQuit = (lStart - 86400) + TimeOutValue             bPastMidnight = True         End If     End If     lInst = Shell(sExeName, vbMinimizedNoFocus)      lProcessId = OpenProcess(PROCESS_QUERY_INFORMATION, False, lInst)     Do         Call GetExitCodeProcess(lProcessId, lExitCode)         DoEvents         If TimeOutValue And Timer > lTimeToQuit Then             If bPastMidnight Then                  If Timer < lStart Then Exit Do             Else                  Exit Do             End If     End If     Loop While lExitCode = STATUS_PENDING          ShellandWait = True    ErrorHandler: ShellandWait = False Exit Function End Function

Edit for spellllllling

[Edited by ~Bean~ on 10-06-2003 at 09:11 AM GMT]

____________________________
Eggheads unite! You have nothing to lose but your yolks.

I don't know what shell is, this what I write:

RetVal = Shell("O:BrioQry 5.5Programbrioqry.EXE", 1)
        channelNumber = Application.DDEInitiate(app:="BRIOQRY", topic:="COMMAND")
        
MyQuery = "location of query.bqy"
MyExport = "where to put the data.csv"
        
        With Application
            .DDEExecute channelNumber, "open brioquery, '" & MyQuery & "'"
            .DDEExecute channelNumber, "connect logon root,'silent' "
            .DDEExecute channelNumber, "process doc root"
            .DDEExecute channelNumber, "activate section root.'pivot'"
            .DDEExecute channelNumber, "export section root.'pivot', 'csv', '" & MyExport & "'"
            .DDEExecute channelNumber, "quit brioquery, 'silent'"
            .DDETerminate channelNumber
      End With

sure you do...at least you're using it anyways...

quote:

---

I don't know what shell is, this what I write:

RetVal = Shell("O:BrioQry 5.5Programbrioqry.EXE", 1)
        channelNumber = Application.DDEInitiate(app:="BRIOQRY", topic:="COMMAND")

---

Ok cool, I'll give it a whirl!

I can't quite grasp the shell and wait bit. I have gone and leant about the shell function and now understand more precisley what my question is.
I set the original macro to run at 7am in the morning. Aswell as sending the message out of snyc. it also gets really upset when I ask it to go back into excell and start copying cells. Because it is still messing around in the other app it just errors on a simple copy of a selection. So I need to halt my excel proceedures until the other app has finished.
Could you modify the shell and wait accordingly with my bit of code??
Thanks

Is all of this written in Excel?

If so, I'm not sure that the Shell and Wait will work (I am currently working on it).

Having Excel open and waiting for the task to run/finish may not be the best way to go about it anyway...commonly, programmers use either Windows Scheduled Tasks OR their own timers in a second EXE to initiate the timed task. If you must use only Excel, and if you must give the user the option of setting the task time, you will run into some difficulties...like what if the user shuts down Excel? or the Computer? and Excel is using system resources the whole time (even if it is hidden), and the problems you mentioned of trying to use Excel while it is waiting.

I am thinking you should use Scheduled Tasks... this can be accessed through the WinAPI (or you can setup the task manually, which would be much easier)...I apologize for my ignorance but I am not sure how to implement these API's - I have never used them before...but I do believe they can be used from Excel VBA...

NetScheduleJobAdd
NetScheduleJobDel
NetScheduleJobEnum
NetScheduleJobGetInfo
  

I will keep looking for more info on this as well...

____________________________
Eggheads unite! You have nothing to lose but your yolks.

Currently it's all written in Excell, but I can change it. So I'll try using scheduled tasks.
Thanks very much.