Solved

running shell command in vba with error handling

Posted on 2013-01-17
7
1,688 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
Independent Software Vendors: 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

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!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

739 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