Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


running shell command in vba with error handling

Posted on 2013-01-17
Medium Priority
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
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
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?

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 26

Accepted Solution

redmondb earned 1800 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 200 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

721 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