Call a .bat file from VBA

cmhoz

Active Member
Joined
Aug 20, 2006
Messages
268
Hi All -

I've been playing with using VBA to create a .bat file which is then called to get a file off an FTP.

Right now - the VBA creates a .txt file and a .bat file. If I run the .bat file manually once it's been created, it works. BUT - when VBA calls the .bat file, I can see it open and appear to run, but the file I'm telling it to get is not got.

I've tried a few different versions of the Shell, the one I'm using now is the only one I've tried that actually gets the .bat file to open. But maybe I'm missing something?? I've spent many hours on google and searching the mrexcel boards, and I've found lots of stuff that says my code should work and nothing to tell me what's wrong!

Here's what I have... and help would be much appreciated!

Code:
Sub PublishFile()
Dim strDirectoryList As String
Dim lStr_Dir As String
Dim lInt_FreeFile01 As Integer
Dim lInt_FreeFile02 As Integer


'On Error GoTo Err_Handler
    lStr_Dir = ThisWorkbook.Path
    lInt_FreeFile01 = FreeFile
    lInt_FreeFile02 = FreeFile

    '' ANW  07-Feb-2003 :
    strDirectoryList = lStr_Dir & "\Directory"

    '' Delete completion file
    If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList & ".out")

    '' Create text file with FTP commands
    Open strDirectoryList & ".txt" For Output As #lInt_FreeFile01
    Print #lInt_FreeFile01, "open myftp"
    Print #lInt_FreeFile01, "myusername"
    Print #lInt_FreeFile01, "mypassword"
    Print #lInt_FreeFile01, "cd source/myfolder"
    Print #lInt_FreeFile01, "binary"
    Print #lInt_FreeFile01, "get constructionBG.gif"
    
    Print #lInt_FreeFile01, "bye"
    Close #lInt_FreeFile01

    '' Create Batch program
    Open strDirectoryList & ".bat" For Output As #lInt_FreeFile02
    Print #lInt_FreeFile02, "ftp -s:" & strDirectoryList & ".txt"
    Print #lInt_FreeFile02, "Echo ""Complete"" > " & strDirectoryList & ".out"
    Close #lInt_FreeFile02

    ' Call the batch file
    Call Shell(Environ$("COMSPEC") & " /c " & strDirectoryList & ".bat", vbNormalFocus)
    
    ' Wait for completion
    Do While Dir(strDirectoryList & ".out") = ""
        DoEvents
    Loop


    '' Clean up files
    If Dir(strDirectoryList & ".bat") <> "" Then Kill (strDirectoryList & ".bat")
    If Dir(strDirectoryList & ".out") <> "" Then Kill (strDirectoryList & ".out")
    If Dir(strDirectoryList & ".txt") <> "" Then Kill (strDirectoryList & ".txt")

bye:

Exit Sub

Err_Handler:
    MsgBox "Error : " & Err.Number & vbCrLf & "Description : " & Err.Description, vbCritical
    Resume bye

End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Humour me here and try changing:

Print #lInt_FreeFile01, "get constructionBG.gif"


to

Print #lInt_FreeFile01, "get constructionBG.gif*"

Also, what happens if you click the batch file on it's own ie rather than letting the code run it?
 
Upvote 0
If I click on the .bat file and run it manually, it works. So - I don't think there's anything wrong in the FTP syntax, but that the issue is in the way I'm calling the .bat file? Or maybe it IS getting my file, but it's putting it someplace random that I can't find??


Adding a "*" to the get file makes it not work when I run it manually, and doesn't change the VBA behaviour...
 
Upvote 0
Hmmmm oddness. You are changing the DIR in your FTP script so it should download to the correct spot. The * was a long shot but we had some issues with an auto script putting files years ago and putting the * at the end resolved it, dunno why.

Does the code wait for the command prompt box to dissapear before continuing? Maybe put a pause in there for a reasonable amount of time to allow completion after calling the batch file.
 
Upvote 0
OOH!

I found where it's putting my file!

I think I need to do a chdir in the VBA as well, because it's getting my file, but putting it in some random folder - must be the last place I told excel to open a file from or something.
 
Upvote 0
It was in the FTP syntax after all....

I needed an extra line to tell it in which directory to put the 'got' file.

Print #lInt_FreeFile01, "lcd C:\OOD"


Phew! Now to see if I can make it PUT a file!
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top