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

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

--
Message posted from http://www.ExcelForum.com

0
5/15/2004 5:50:50 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
421 Views

Similar Articles

[PageSpeed] 47

Are you trying to wait for your DOS(?) command to finish?

http://support.microsoft.com/?kbid=214248
XL2000: How to Force Macro Code to Wait for Outside Procedure

Here's a link to a nice ShellAndWait function that does that.
http://groups.google.com/groups?threadm=ump2dlfcBHA.1656%40tkmsftngp03

"benazir <" wrote:
> 
> Hi,
> 
> I am using a VB script in order to perform Operating system task via
> 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 Command
> prompt.
> 
> But after the completion of backup we can't know when its end or is it
> successful or not.
> 
> I wanted that when this activity completes, a message box appear on
> EXCEL, which informs that the task is completed and its status
> (successful or unsuccessful).
> 
> What extra piece of code I add in the following code in order to
> 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"
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/15/2004 11:28:12 AM
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

--
Message posted from http://www.ExcelForum.com

0
5/16/2004 6:24:25 AM
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......."



"benazir <" wrote:
> 
> 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 in
> 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 Excel
> "suspended"
> ' until the custom application is complete. This will occur while
> the
> ' length of the FindIt variable is equal to 0. Microsoft Excel will
> ' remain busy until it finds the file Flag.txt, thereby making the
> 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"
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/16/2004 11:26:02 AM
Reply:

Similar Artilces:

Office 2004 alert message
I get the following alert message shortly after opening Excel or Word (the only applications that I use): "An unexpected error occurred while trying to load the Microsoft Framework library" Then I can continue after acknowleding the alert, apparantly with no ill effects. I have used the "Remove Office Tool" to clean out any old files remaining (I've done this a couple of times), then reinstalled Office from the original CD, but this still happens. Hi Buster - Are you Repairing Disk Permissions (Disk Utility) after the install? -- HTH|:>) Bob Jones [MVP] Office...

Message stuck in queue in Exchange 2003
I have two messages that are stuck in queue of ESM. ESM doesn't give me the option to delete these messages. The sender is blank if that helps. -- Eric Sabo NT Administrator Which queue? What is the status of the messages? -- Ben Winzenz Exchange MVP "Sabo, Eric" <sabo_e@cup.edu> wrote in message news:%23BBIwBG3EHA.1300@TK2MSFTNGP14.phx.gbl... >I have two messages that are stuck in queue of ESM. ESM doesn't give me >the option to delete these messages. The sender is blank if that helps. > > > > -- > Eric Sabo > NT Adm...

Dual Displays
I want to connect my Sharp Aquos flat screen TV to my computer, and see my desktop on both the TV and computer monitor. Windows Vista Home Premium. HP Pavilion a1610n computer. Galaxy GeForce 8400 GS graphics card, outputs: 1-VGA, 1-DVI, and 1-SVGA. There is no HDMI port on the card. I connected a VGA cable between the motherboard "on-board" VGA port and the TV, figuring that I could use that output to work with the TV. The computer monitor is plugged into the GeForce graphics card. Tried the Desktop "Display Setting". It says, the number 2 monitor is "N...

Folder could not be displayed
I am running Windows XP and Outlook Express 6 just fine then one day my husband does something to my computer and his email account won't work. My email is working just fine but in his he cannot open his inbox. It says folder could not be displayed, and when you send and receive it gives you this error message: "An unknown error has occured. Account: 'incoming.verizon.net'. Server:'incoming.verizon.net'.Protocol:POP3, port: 110, Secure(SSL): No, error number: 0x800C013B" If anyone could help me with this I would greatly appreciate it. Thank you T...

Display a message box "this entry is a duplicate"
How can I display a message box on a form if the shipment number is a duplicate? I need a message box to pop up when user attempts to tab out of the field. Thank you. Something like: Private Sub txtShipNum_BeforeUpdate(Cancel As Integer) If DCount("*", "MyTable", "ShipNum = '" & Me.txtShipNum & "'") > 0 Then Msgbox Me.txtShipNum & " already exists." Cancel = True End If End Sub This assumes that your text box is named txtShipNum, that the name of the field in table MyTable is ShipNum...

Receiving own message
My company is running Exchange 2000 with Outlook XP. For my department, we have set up a distribution list with everyone's e-mai in it. When I send a message to that group, I receive the message I sent since my name is in the distro list. Is there an automated way to stop receiving the e-mails I sent? Thanks for any advice. You could create that deletes anything sent from you to that list. -Peter <chip33az@netscape.net> wrote in message news:1191435573.387243.65910@y42g2000hsy.googlegroups.com... > My company is running Exchange 2000 with Outlook XP. > > For my depar...

Exchange 5.5 rejecting large messages
Can't find any postings about this, so here goes: When a message is sent to my Exchange server from the internet, with an attachment larger than about 2MB, the sender gets a failure notice. The error is a 552: exceeded storage allocation exceeded. There are a couple of strange things about this: 1) I have NO limits on attachment size at any level in Exchange. 2) This only started to happen after I switched ISPs. When I look at the full text of the returned message, this is the last line: "Engine timed out. Please contact the system administrator and report what caused thi...

Message Box On One Sheet Only
I have a large spreadsheet with multiple worksheets in it. When one specific worksheet is selected (it does the calculations used on the other pages) I would like a pop-up text box to appear warning the user not to print it. Is there a relatively easy way to do this given that I can't write VB (though I can follow it in the VB editor)? I have been able to create a pop-up that appears on every page by putting an Auto_Open Msgbox in the ThisWorkbook module, but can't figure out how to get it to work on only one worksheet - is this a syntax probem or can't it be done?. Anoth...

error message when I open outlook
when I open out look I get an error messsage that says error message 0x8E54010f .DLL not found PSTPRX.DLL and I can't do anything with outlook. How can I resolve this problem >-----Original Message----- >when I open out look I get an error messsage that says >error message 0x8E54010f .DLL not found PSTPRX.DLL and I >can't do anything with outlook. How can I resolve this >problem >. > ...

Excel Slooooow. It takes 4 to 6 seconds to perform each input or command
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have just installed Excel 2008 on a new installation of Snow Leopard and it does not work properly. It takes ages to register every input or command, like a large Lag. <br><br>EG. each figure or text, input into cells takes 4 seconds from hitting enter to accepting the input. Changing the width of a column takes 6 seconds from letting go of the mouse after dragging to the new width. etc etc. <br><br>I have checked for updates. Any ideas? > I have just installed Excel 2008 on a n...

Configure Outlook
Can ANYONE HELP ME, please? Question based on Microsoft Exchange Server 5.5 on Lan. I send a message to receiver A and receiver A FORWARDS my message to receiver B, My Question is: How can I configure outlook from my workstation so that receiver B reads the e-mail without my email address and Name (My personal Particulars). To simplify: I do not want my e-mail address and Name to be included in the forwarded messages; E-mail originated from me. Thank you... RKP ...

how to display values in 3 cells into one cell
Anyone can help me how do I display the 3 separate different values in 3 cells into just one single cell ? thanks so much in advance. Either there was a typo or there's an echo in here. That line should have been: = A1 & " " & B1 & " " & C1 "Bradley Dawson" <bradleydawson@earthlink.net> wrote in message news:l464b.2224$Lk5.2093@newsread3.news.pas.earthlink.net... > In the target cell, type: > > = A1 & B1 & C1 > > where A1, B1, C1 are the cell addresses that you want to concatenate. > > If these have number...

open excel file
i have problem with a excel file. the file is not to big. its has 5 tabs only. and when i click on to one of the "tabs" the excel gives me a error message as "not reponding". and this happens with this particular tab only. any help "Rohit" wrote: > i have problem with a excel file. the file is not to big. its has 5 tabs > only. and when i click on to one of the "tabs" the excel gives me a error > message as "not reponding". and this happens with this particular tab only. > > any help Try looking at thi...

Excel Formulas #11
I would like to create a sumation formula that will pull in different values depending upon what I need. For example, I want to add values in column A, however sometimes I want to go through row 10 and the next time through row 20. So I want to create a formula that will give the value depending on the the row number I type in. For example if I type in 15 then I will get a sum of variables in column A of rows 1 through 15. If I type in 20 then I will get a sum of variables in rows 1 through 20. How do I create such a formula? Any help would be much appreciated. -- ag_banker_kansas You ca...

download error message
When attempting to download my statement in Money 2004, I get the error message "The file you attempted to import appears to be invalid or contains corrupt data. Contact your bank." I contacted my bank (B of A). They said the problem was with Microsoft Money. The download was working fine until about 3 weeks ago. How do I fix the problem? In microsoft.public.money, MILTON wrote: >When attempting to download my statement in Money 2004, I >get the error message "The file you attempted to import >appears to be invalid or contains corrupt data. Contact >your ba...

How to select & display distinct values
Hello, I need help with a form please. My form has two controls, the first being a combo box. This box should select the company name from a lookup table. The lookup table has only two companies in it, however, they each are repeated several time. Company 1 has about 10 rows, company 2 has 3 rows. I used the query builder and came up with the statement: "SELECT DISTINCT Lktbl_Role_Master.CompanyName FROM Lktbl_Role_Master;" When I run the statement directly from query view, it executes perfectly and returns exactly two rows (one for each company, in a datasheet view) ho...

In nameing a sheet in Excel it puts a .xls] in front why?
Every time I go to name a sheet it puts an .xls] in front of it and I do not know why Did you try to put brackets around the name? -- Don Guillett SalesAid Software donaldb@281.com "EZE" <EZE@discussions.microsoft.com> wrote in message news:C9045E70-AD04-4C91-B223-8851A0DFE55D@microsoft.com... > Every time I go to name a sheet it puts an .xls] in front of it and I do not > know why Remove the bracket or other illegal character(s) from the workbook name. In article <C9045E70-AD04-4C91-B223-8851A0DFE55D@microsoft.com>, EZE <EZE@discussions.microsoft.com>...

Display Time in Outlook
The time setting in my Outlook is one hour behind my actual time. I cannot determine how to change the time. Outlook is the only item that is incorrect. Other display times in operating system are correct. Thanks, Jodi ...

Activate my Excel 2003 but it still runs in reduce mode
My Excel 2003 is showing that it's activated but it is still running in reduce mode. what I'am doing wrong? Rightclick on the excel icon on the windows taskbar (usually at the bottom of the screen). Choose Maximize. Maybe it's just off the screen (not really minimized--just off the screen). atomicesar wrote: > > My Excel 2003 is showing that it's activated but it is still running in > reduce mode. what I'am doing wrong? -- Dave Peterson ...

PK and FK Displayed
I am a complete Visio newbie, and cannot find the answer I need in books or online help. I am trying to create ER diagrams from scratch, and not connected to real tables, using VEA. I have added tables ('Entities') to the form, connected them with a Relationship connector, and, in the Database Properties window I've assigned the end points to a Primary Key on the left and a Foreign Key on the right. But the model shows the connector as running between the top colums on both sides, whereas I want the keys to be in that upper box and provide the connector's end points. Wh...

Formulas are displayed, results are not
Hello, For some reason, when I enter a formula into a cell and press enter, the formula just stays there. Anyone know what I can do to show the results? I tried pressing Cntrl + `, but that didn't work. FYI, when I cut and paste a column from a separate workbook, that column works like normal (i.e., the formulas produce results). Thanks for any suggestions! Hi Mike, Maybe your cell is preformatted as Text. Try formatting to general then erase your = and type it in again. HTH Martin "Mike C" <js2k111@yahoo.com> wrote in message news:f699bcd4-ad82-4a88-9759-5638c147b...

reading data from Excel Sheet
hi, i want to read the data from the Excel sheet using vc++6.0. How can i read it. i don't want to create any DSN thank you, regards, koti You can use the Excel.Application com object -- Satish "Koti" wrote: > hi, > > i want to read the data from the Excel sheet using vc++6.0. How can i > read it. i don't want to create any DSN > > thank you, > > regards, > koti > > > ...

Excel error message not understood
Has anyone ever gotten the error message "Too many different formats"? If so, what exactly does it mean and what can a user do about it? The following MSKB article has information on the error message: XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?id=213904 Jimbo wrote: > Has anyone ever gotten the error message "Too many > different formats"? If so, what exactly does it mean and > what can a user do about it? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Substitue user message in place of system-generated error message
There are often times when I would like to isuue my own error message in place of a system generated error message. A good example would be when a message appears that indication a referential integrity violation or cascading delete warning. I would like to customize these error messages and warnings for my user. Is it possible to do this? Thanks, Gerry Goldberg yep you can do this in most cases in VB code. should be lots of resources online, here is one: http://allenbrowne.com/ser-23a.html If you let Access automatically program a command button by going through the wiza...

Excel 2007 does start up right
I have been using Excel 2007 since about March with no problems. Recently I started experiencing a problem when the application is stated. It randomly (maybe 2 out of 6 times) does not open to the "grid" screen but rather a light blue screen with nothing on it except the Ready button in the upper left corner. If I click on that button I get the same drop down menus as I get when the application starts correctly and I click on the Ready button in the lower left corner. This has only been happening for about the last two weeks. If I just open it and close it several times withou...