• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 720
  • Last Modified:

VBA shell command fails unless manually stepped through

This Visual Basic for Applications code is part of a longer subroutine. Interestingly, the code works just fine on one of the 2 computers that I am trying to use it on.
On the failure box, the xcopy fails if the subroutine is allowed to run, or if a breakpoint is placed just before the bold line, and then you press F5 after it breaks.
If you wait for the break and press F8 to step through the bold line, the xcopy succeeds.

I considered a number of differences between the 2 boxes, including the failure one having less RAM (didn't fix it), whether the various drives referred to by Y:\ or C:\ were local or on the LAN (didn't fix it), whether the various drives were in "Trusted Zones" (didn't fix it). I also tried putting a pause using Application.OnTime Now()+ 30 seconds or so (didn't fix it).

Also, the computers are plugged in and powered on, ha ha.

        DrivieLetter = "Y"
        ChDrive DrivieLetter
       
        On Error Resume Next
        MkDir "Y:\StoreToMove"
        ChDir "Y:\StoreToMove"
        MkDir XRaySourceFolder
        XRayDestinationFolder = DrivieLetter & ":\StoreToMove\" & XRaySourceFolder
       
        ChDir "C:\Genesis Digital Imaging\Omni-Vue\Image\"
        XRaySourceFolder = "C:\Genesis Digital Imaging\Omni-Vue\Image\" & XRaySourceFolder
        Set fs = CreateObject("Scripting.FileSystemObject")
        fs.CopyFolder XRaySourceFolder, XRayDestinationFolder
       
       Shell "xcopy Y:\StoreToMove Y:\Roentgen\Xrays\XrayImages /e"
0
gnarlyman
Asked:
gnarlyman
  • 6
  • 3
  • 2
  • +5
1 Solution
 
vguzmanIT ManagerCommented:
try:
Shell "CMD /c " & "xcopy Y:\StoreToMove Y:\Roentgen\Xrays\XrayImages /e"
0
 
gnarlymanAuthor Commented:
Thanks for that suggestion; I will have to check it tomorrow at my office, and I'll let you know how perfectly it works
0
 
gnarlymanAuthor Commented:
Tried the additional command, but unfortunately the outcome was the same. Very briefly, a command-line window made its appearance on the task bar (this happened with the previous code, too), but the copying didn't happen.
I am open for more suggestions.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
vguzmanIT ManagerCommented:
What about using
Shell "CMD /k" & ...
and see whats the error showing after the command line ? the /k should keep the command line open
0
 
gnarlymanAuthor Commented:
With the /k switch (thanks for that tip; I don't have any real fluency with command-line commands), again a no-go.

In the attached .doc, the upper window is the appearance after a failed copy; really no help at all.
(The lower is the appearance after using F8)
 scshot.doc

I remain open for still more suggestions
0
 
vguzmanIT ManagerCommented:
ok, lets try:

Shell "CMD /k" & "Y:\StoreToMove\*.* Y:\Roentgen\Xrays\XrayImages\ /E /Y /C > Y:\Roentgen\Xrays\XrayImages\Copy_log.txt"

xcopy is case sensitive, make sure you use upper case switches, just in case =)
/Y will copy and overide without prompts
/C will continue copying even after an error

post Copy_log.txt here to troubleshoot.
0
 
gnarlymanAuthor Commented:
Same result, I'm afraid.
Copy_log says (in its entirety)

0 File(s) copied
0
 
gnarlymanAuthor Commented:
Thank you, V Guzmán, for all your efforts.
I'm concluding by your silence past 2 days that I've got you stumped.
Anyone else care to take a stab at my problem?
0
 
dlmilleCommented:
I may be stretching here, but XCOPY can fail for several reasons.  Let's look at an "obvious" one (but not so obvious when it works on some, but not others)...

Since it worked on some computers and not others, can you ensure that the directories you're referencing in the base command exist on the Y: drives of the computers that don't work?

Shell "xcopy Y:\StoreToMove Y:\Roentgen\Xrays\XrayImages /e"

Does Y:\StoreToMove exist on the failing computer?

Does Y:\Roentgen\Xrays\XrayImages exist on the failing computer?

Is the Y: drive a LAN drive?  Perhaps the failing computer also has a local Y: drive?  Worth a check to see.

If this is the problem (directory doesn't exist), you can create the directories manually, or put mkdir commands in your script.  If the drive exists already on the failing computer and its SUPPOSED to be a network drive, well, you can modify the script or redirect the drive letters.
0
 
Nico BontenbalCommented:
I've had similar problems where stepping through the code worked, but running the code didn't. I was able to solve this by letting the code wait 1 second after the command. Maybe this works for you too.
Use method 2 in the kb article:
http://support.microsoft.com/kb/162150
Then change your code to:
....
Sleep 1000
fs.CopyFolder XRaySourceFolder, XRayDestinationFolder
Sleep 1000        
Shell "xcopy Y:\StoreToMove Y:\Roentgen\Xrays\XrayImages /e"
Sleep 1000

Open in new window

If this works you can experiment with removing and shortening the sleep times.
0
 
dlmilleCommented:
Ahh - I missed that part about stepping through working, after having waded to the bottom of the thread.  My post is not relevant, having observed that.

Good catch, Nicobo
0
 
Steve KnightIT ConsultancyCommented:
OK.  The fact it says 0 files copied suggests nothing there?

Either add /l and /f to the xcopy line you have (show full paths of files, show files that would be copied but not actually do it..) or would suggest write yourself this code into a batch file in the same dir.  Make it copyit.cmd or something... or have attached it too.

set Source="%~1"
set Dest="%~2"
pause
@echo off
if exist %source% (
  echo Source %source% exists
  dir %source%
) ELSE (
  echo Source does not exist
)
if exist %dest% (
  echo Dest %dest% exists
  dir %dest%
) ELSE (
  echo Dest does not exist
)
echo About to do copy
pause
xcopy /E /L /C %source% %dest%
pause

 copyit.cmd

Then we can see what it is trying to do if you change your shell command to :

shell "c:\somedir\copyit.cmd Y:\StoreToMove Y:\Roentgen\Xrays\XrayImages"


I'm also a little confused by your VBScript too, though I guess it part of bigger, or has been amended to post too:

What is XRaySourceFolder?  

It seems your process goes, based on this source folder "ABC123"

md "y:\storetomove"
cd "y:\storetomove"
md ABC123
(so now you have "Y:\storetomove\abc123"

XRayDestinationFolder = DrivieLetter & ":\StoreToMove\" & XRaySourceFolder

So XRayDestinationFolder = "Y:\storetomove\abc123"  now too.

Then you change to

CD "C:\Genesis Digital Imaging\Omni-Vue\Image\"

and set the same source folder variable to

"C:\Genesis Digital Imaging\Omni-Vue\Image\ABC123"

Then you are trying to do a copy folder of
"C:\Genesis Digital Imaging\Omni-Vue\Image\ABC123" to "Y:\storetomove\abc123"

And then doing an xcopy of the whole y:\storetomove directory to Y:\Roentgen\Xrays\XrayImages

0
 
gnarlymanAuthor Commented:
Nicobo, what you're suggesting with the "Sleep" function is what I tried to do using Application.OnTime Now()+ 30 seconds or so (didn't fix it).
However, your suggestion worked like a charm! Thank you very much.
Just curious, any insight into why Sleep worked and .ontime didn't?

Dragon-it, thanks for yours, but Nicobo's solution solved the problem. XRaySourceFolder is, indeed, defined several lines farther up the code.
0
 
JSRWilsonCommented:
The problem with Shell commands is that the next line of code does NOT wait for the shell command to complete and this is probably the source of the error.

If you google for Shellandwait you should find routines to set the wait time.
0
 
LambertHeenanCommented:
A couple of observations about your script code.

You have a line near the start of the code which says

MkDir XRaySourceFolder

but nowhere do you set a value for XRaySourceFolder, perhpas you do that in another line of code not shown.

Second, why are you even using shell and xcopy at all? You could change the second to last line of code to...

fs.CopyFolder XRaySourceFolder & "\*.*", XRayDestinationFolder

and that would copy all the files and folders in the source folder. You can then remove the call to Shell.

See http://msdn.microsoft.com/en-us/library/xbfwysex(v=vs.85).aspx
0
 
Nico BontenbalCommented:
Application.OnTime is for scheduling a macro isn't it? This way your code will run a bit later, but it will run in the same manner as when executed directly. Sleep will actually pause your code. Just like when stepping through the code. I don't know why this fixes the problem. I think the problem occurs on faster computers. Seem as if they run ahead of themselves. As if the code finishes before the shell command is properly started and this causes the shell command to be cancelled. Or in your case it might be because the next copy command is started before the previous one is completely finished.
When I was young I used to spent hours and hours trying to find out why a particular solution worked, but these days I'm just happy to have found a solution and move on to the next task :-)
0
 
Nick67Commented:
I see you have it solved and know that you have to sometimes sleep your code when you've got external things happening is valuable...
but I have to agree with @LambertHeenan
<why are you even using shell and xcopy at all? >

You are firing up a FileSystemObject in the code.  Use it!
Use it to check and see if your drives exist -- bail if the drive mappings aren't there
Use it to check and see if your folders exist -- use it to create them if they don't
Use it to check and see if your files exist -- and then use it to copy them if they do

       Set fs = CreateObject("Scripting.FileSystemObject")
        fs.CopyFolder XRaySourceFolder, XRayDestinationFolder

       
      Shell "xcopy Y:\StoreToMove Y:\Roentgen\Xrays\XrayImages /e"
0
 
JSRWilsonCommented:
Agree - don't use Shell here!

Sleep will pause the code and not the shell so it does what you need but you are only guessing at how long to sleep for! Shellandwait does solve this but you should use the fSO
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 3
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now