How to Display Message after the completion of OS task in EXCEL.

  • Thread starter Thread starter benazir
  • Start date Start date
B

benazir

Hi,

I am using a VB script in order to perform Operating system task vi
EXCEL.

The routine, which is written in Excel VB, take a backup of files.

When the code runs it’s begin backup of several OS files via Comman
prompt.

But after the completion of backup we can't know when its end or is i
successful or not.

I wanted that when this activity completes, a message box appear o
EXCEL, which informs that the task is completed and its statu
(successful or unsuccessful).

What extra piece of code I add in the following code in order t
accomplish the desired result?

Could some please inform?

Thanks


Regards

Benazir





SAMPLE CODE:



strFileOut="c:\backup.sql"
set objFS=CreateObject("Scripting.FileSystemObject")
set objOutFile=objFS.OpenTextFile(strFileOut,2,1)
strOutput="backup datafile <datafile Name>;"
objOutFile.WriteLine strOutPut
set WShShell=CreateObject("WScript.Shell")
WShShell.Run "rman target / @c:\backup.sql
 
Hi Dave,

Yes, I am trying to wait for the Dos command to finis.
Thanks for giving the very useful information about my problem.
But I still need some suggestion from you.
I got the following code from your recommended site:
But where should I adjust this code in my small routine.
Could you please also assist me in order to adjust the expert’s code i
my routine?
Below you find my code and the recommended code.

Thanks

Benazir





Recommended Code:



Sub Appacttest()

' Checks to see if Flag.txt already exists.
FindIt = Dir("C:\Flag.txt")

' If the file Flag.txt has been found then delete it.
If Not Len(FindIt) = 0 Then
Kill "C:\Flag.txt"
End If

' Sets Myapp variable equal to the Shell statement.
Myapp = Shell("C:\Custom.exe", 1)

' Executes the shell statement.
AppActivate Myapp

' Checks to see if Flag.txt can be found yet.
FindIt = Dir("C:\Flag.txt")

' The following While Wend loop will keep Microsoft Exce
"suspended"
' until the custom application is complete. This will occur whil
the
' length of the FindIt variable is equal to 0. Microsoft Excel will
' remain busy until it finds the file Flag.txt, thereby making th
length
' of FindIt > 0 and ending the loop.

' Check to see if the length of FindIt variable is equal to 0
' chars.
While Len(FindIt) = 0

' Continue to check if flag was created yet.
FindIt = Dir("C:\Flag.txt")

Wend

' Continue with more code if needed.

End Sub




My Code:

strFileOut="c:\backup.sql"
set objFS=CreateObject("Scripting.FileSystemObject")
set objOutFile=objFS.OpenTextFile(strFileOut,2,1)
strOutput="backup datafile ;"
objOutFile.WriteLine strOutPut
set WShShell=CreateObject("WScript.Shell")
WShShell.Run "rman target / @c:\backup.sql
 
If you use this first option, your other application (rman) will have to create
c:\flag.txt.

I think I'd use the second version:

(untested: I don't do SQL and don't have rman anywhere in my path.)

Option Explicit
Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103

'Window States (Per Help for Shell function):
' 1, 5, 9 Normal with focus.
' 2 Minimized with focus.
' 3 Maximized with focus.
' 4, 8 Normal without focus.
' 6, 7 Minimized without focus.
Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
Dim hProg As Long
Dim hProcess As Long, ExitCode As Long

'fill in the missing parameter and execute the program
If IsMissing(WindowState) Then WindowState = 1
hProg = Shell(PathName, WindowState)

'hProg is a "process ID under Win32. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub

Sub Test()

Dim myCmdLine As String
Dim strFileOut As String
Dim objFS As Object
Dim objOutFile As Object
Dim strOutPut As String

strFileOut = "c:\backup.sql"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objOutFile = objFS.OpenTextFile(strFileOut, 2, 1)

strOutPut = "backup datafile ;"
objOutFile.WriteLine strOutPut
objOutFile.Close

myCmdLine = "rman target / @c:\backup.sql"
ShellAndWait myCmdLine, 1

End Sub

(and I think I'd explicitly give the paths of that stuff in the command line:

mycmdline = "C:\myfolder1\myfolder2\rman......."
 
Back
Top