Solved

running shell command in vba with error handling

Posted on 2013-01-17
7
1,660 Views
Last Modified: 2013-06-26
Hi,

I`m using shell command with xcopy in vba\excel
my problem is that i don`t have error notification.

PID=shell ("xcopy c:\test.txt c:\11\erc.dat /Y")

excute this line give no error , but the file test.txt is not copied into 11 folder.
also if test.txt is not existed it will not showing any error..
how to solve this problem.
thxn in regards:}
0
Comment
Question by:drtopserv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38786304
Hi, drtopserv.

I've used two different approaches...
(1) Before the xcopy check for the output file and delete it if it exists. After the xcopy check that it the output file exists.
(2) Write the command to a temporary batch file, appending " 2> Error.txt". After you run the batch file check that Error.txt is empty - if not it contains your error message.

Regards,
Brian.
0
 

Author Comment

by:drtopserv
ID: 38786341
i need to run this code in vba in a form in excel 2010
means when i click the commandbutton in this form in excel , it will execute the shell command.
i need a pop-up window telling me if the process is not done,. and msgbox error .
how may i do it?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38786700
drtopserv,

I found an old example of the check I described in (1). Unfortunately it has lots of silly 32-bit code for waiting for the Shell'ed program to complete. How does your code do that?

Thanks,
Brian.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Accepted Solution

by:
redmondb earned 450 total points
ID: 38786924
drtopserv,

OK, below is an example of the batch file approach. Please note...
(1) More 32-bit code, I'm afraid. I've no access to a 64-bit machine so I can't tell you what, if anything, you'll have to do to make this work.
(2) Don't forget to change the four file locations at the start of Run_Batch().

Regards,
Brian.
Option Explicit

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Run_Batch()
Dim i           As Long
Dim xFileNumber As Long
Dim xBatch      As String
Dim xInput      As String
Dim xOutput     As String
Dim xCheck      As String
Dim o_FSO       As Variant
Dim o_File      As Variant


xBatch = "D:\$TEMP$.Bat"
xCheck = "D:\$TEMP$.Flg"
xInput = "D:\Test_Input.txt"
xOutput = "D:\Test_Output.txt"

' Delete old work files...
If Dir(xBatch) <> "" Then Call Kill(xBatch)
If Dir(xCheck) <> "" Then Call Kill(xCheck)

' Write the batch file
xFileNumber = FreeFile()
Open xBatch For Output As xFileNumber
Print #xFileNumber, "@Echo On"
Print #xFileNumber, "echo Fred | find "" Not_Fred"" > """ & xOutput & """" 'Create an empty output file to avoid XCopy prompt.
Print #xFileNumber, "xcopy """ & xInput & """ """ & xOutput & """" & " /F /Y"
Print #xFileNumber, "Echo OK > """ & xCheck & """"
''''Print #xFileNumber, "Pause"
Close #xFileNumber

Debug.Print Shell(xBatch, vbMinimizedNoFocus)

Do Until Dir(xCheck) <> ""
    DoEvents
    Sleep 1000
    i = i + 1
    Debug.Print "Delay " & i
    If i > 60 Then
        MsgBox ("No result from XCopy after 60 seconds - run cancelled.")
        Exit Sub
    End If
Loop

Set o_FSO = CreateObject("Scripting.FileSystemObject")
Set o_File = o_FSO.GetFile(xOutput)
If o_File.Size = 0 Then
    MsgBox ("The following failed - run cancelled." & Chr(10) & Chr(10) & "XCopy """ & xInput & """ """ & xOutput & """")
    Exit Sub
Else
    MsgBox ("The following succeeded." & Chr(10) & Chr(10) & "XCopy """ & xInput & """ """ & xOutput & """ - succeeded.")
End If

End Sub

Open in new window

0
 
LVL 16

Assisted Solution

by:terencino
terencino earned 50 total points
ID: 38796201
Probably a bigger issue is why it didn't copy the file in the first place. I did a trail run (on Windows 8 Pro, but maybe also an issue on Windows 7?) and it wouldn't work until I had Runas Administrator, supplied a password, and then specified if it was a file or folder, both of the last two in command line windows. So perhaps is there another option for you rather than XCOPY? As you are not using many of XCOPY's features in your example, I would recommend FileSystemObject CopyFile method

To check if a file transfer is complete, the FileExists method of FileSystemObject is a flexible option which will put further code execution on hold, or run various other checks. And it is easy to implement the strategy recommend by Brian.

So running it all with FSO, put in a reference to Microsoft Scripting Runtime, and change the Click event for your CommandButton1 as follows
Private Sub CommandButton1_Click()
    sCopyFile
End Sub

Open in new window

... and copy this code into the module:
Function sFileExists(sfolder As String, sfile As String) As Boolean
Dim oFolder As Folder, oFile As File
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Set oFolder = fso.GetFolder(sfolder)

For Each oFile In oFolder.Files
    If fso.FileExists(sfolder & sfile) = True Then
        sFileExists = True
        Exit For
    End If
Next

Set fso = Nothing
Set oFolder = Nothing

End Function

Sub sCopyFile()
Dim sfolder As String, sfile As String, tfolder As String, tfile As String
Dim fso As FileSystemObject
Set fso = New FileSystemObject
sfolder = "C:\"
sfile = "test.txt"
tfolder = "C:\11\"
tfile = "erc.dat"
If sFileExists(tfolder, tfile) Then fso.DeleteFile tfolder & tfile
fso.CopyFile sfolder & sfile, tfolder & tfile, True
Do Until sFileExists(tfolder, tfile)
Loop
MsgBox "File has arrived in folder"
End Sub

Open in new window


Hope that helps
...Terry
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38796361
Hi, Terry.

Always nice to see less code! A few points, though...
(1) If a folder already exists with the same name as the output file then the macro crashes.
(2) If there's a problem with the copy then the "Do Until" locks Excel into an infinite loop - maybe a DoEvents and/or a counter?

Regards,
Brian.
0
 

Author Closing Comment

by:drtopserv
ID: 39277665
thnx guys:}}!!!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question