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?

[Webinar] Streamline your web hosting managementRegister Today

spinzr0Connect With a Mentor Commented:
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
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?

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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.
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?
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

All Courses

From novice to tech pro — start learning today.