VBA Run Batch through Shell


I have a batch file that copies a file from a FTP server to my local computer. The code is at the bottom of this post. This has been working fine for the past week. I now have decided to try running this batch file from my Access database using VBA.

I have the following code in my Access VBA module below. It executes fine & I see the DOS screen flash. However the file from the FTP server is not copied to my computer, unless I double click the batch file myself. I have tried various ShellWait functions with no luck (the code is bit above me), it just flashes the DOS screen before closing it. How can I make my code allow the batch file to finish properly & copy the needed file? Really very stuck on this one!

    Dim ScriptPath As String
    Dim retval As Long
    ScriptPath = "G:\MyWorkFolder\Mark\FTP\DataBatch.bat"
    retval = Shell(ScriptPath, vbNormalFocus)

This is the code in my batch file DataBatch,

ftp -s:DataScript.scp indexftp.SomeWebsite.com

The file DataScript above contains the following code,


prompt n
cd firstFolder
cd secondFolder

get fileName.csv "G:\MyWorkFolder\Mark\FTP\fileName.csv" overwrite


Many thanks

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sub RunBAT()
    Set oWShell = CreateObject("Wscript.Shell")
    oWShell.Run "C:\test.txt", 1, True
End Sub
mcs26Author Commented:
Hi Spinzr,

When running the code you posted I get the following run time error,

Method 'Run' of object 'IWshShell3' failed.

I think my computer does not have the required file & I cannot install anything as my work company are very strict about installing news files that or it takes 6 months! Or could it be another reason?

Are you just trying to copy fileName.csv "G:\MyWorkFolder\Mark\FTP\fileName.csv"?  If so, you can just get rid of the batch and use VB to copy the file.  Let me know.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mcs26Author Commented:
Yes I am just using the batch file as it is the only way I know how to copy a file that is on a FTP server on to my comuter, if you another away in VB the brilliant!
Actually, the method I use wouldn't work as I just use WShell to run FTP.  The only workaround I can think of is calling Start>run (using the shell.application object and sending the command to the run field using sendkeys.  This is not pretty but should work, want me to provide code for this?
I just realized something, the original error might not be valid.  Try to use this

Sub RunBAT()
    Set oWShell = CreateObject("Wscript.Shell")
    oWShell.Run Quote("C:\test.txt"), 1, True
End Sub

Function Quote(sText)
    Quote = Chr(34) & sText & Chr(34)
End Function

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcs26Author Commented:
Hi Spinzr0,

I have just tried the code above, it did execute by it did not copy the file. It appears that VBA focred it to shut before it could finish.  If you believe the send keys could work then by all means please show me the code.

Thanks again
Try using this
Sub RubMyBat()
    Set oShell = CreateObject("Shell.Application")
    Set wShell = CreateObject("WScript.Shell")


    Wscript.Sleep 100

    While Not activated
    Wscript.Sleep 10
    activated = wShell.AppActivate("Run")

    WScript.Sleep 50
    WScript.Sleep 50
End Sub

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft DOS

From novice to tech pro — start learning today.