running shell command in vba with error handling

Posted on 2013-01-17
Last Modified: 2013-06-26

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:}
Question by:drtopserv
  • 4
  • 2
LVL 26

Expert Comment

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.


Author Comment

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?
LVL 26

Expert Comment

ID: 38786700

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?

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

LVL 26

Accepted Solution

redmondb earned 450 total points
ID: 38786924

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().

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) <> ""
    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

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
    MsgBox ("The following succeeded." & Chr(10) & Chr(10) & "XCopy """ & xInput & """ """ & xOutput & """ - succeeded.")
End If

End Sub

Open in new window

LVL 16

Assisted Solution

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()
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

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)
MsgBox "File has arrived in folder"
End Sub

Open in new window

Hope that helps
LVL 26

Expert Comment

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?


Author Closing Comment

ID: 39277665
thnx guys:}}!!!

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

828 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