Solved

running shell command in vba with error handling

Posted on 2013-01-17
7
1,614 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
  • 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

785 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