3

I am trying to automate the following process:

  1. Data imported to Microsoft Excel (myfile.xls) from database and formatted.
  2. File saved as myfile.txt.
  3. Uploaded via FTP to a server.

I have got as far as the FTP upload, but I am having issues with the batch file created within the VBA script.

The current script I have is as follows:

Dim MY_FILENAME As String
MY_FILENAME = "C:\user\sam\test" & "filename" & Format(CStr(Now), "yyyy_mm_dd_hh_mm") & ".BAT"


Dim FileNumber As Integer
Dim retVal As Variant

FileNumber = FreeFile

'create batch file
Open MY_FILENAME For Output As #FileNumber
Print #FileNumber, "FTP 00.0.000.000"
Print #FileNumber, "username"
Print #FileNumber, "password"
Print #FileNumber, "ascii crlf"
Print #FileNumber, "put  " myfile.txt; "  'location'"
Print #FileNumber, "exit"
Close #FileNumber

'run batch file
retVal = Shell(MY_FILENAME, vbNormalFocus)

When I run this script, it opens the IP associated to the server, but fails to enter the login credentials. Can anyone advise where I am going wrong?

braX
  • 11,506
  • 5
  • 20
  • 33
sam c
  • 149
  • 1
  • 2
  • 9
  • I cannot see any Windows [tag:batch-file] in your post, so I removed the related tags; please read the tag info before assigning them... – aschipfl Nov 27 '18 at 12:43
  • 2
    Hi aschipfl, the script above creates a batch file and then runs it. the content of the batch file is: FTP 00.0.000.000, username, password, ascii crlf, put myfile.txt; 'location', exit – sam c Nov 27 '18 at 12:46
  • 3
    @aschipfl it is *obvious* that this post is about creating a batch file !!! – iDevlop Nov 27 '18 at 12:49
  • 1
    @samc it would help if you'd add the declaration of `MY_FILENAME` to the snippet above. – steenbergh Nov 27 '18 at 12:51
  • @steenbergh MY_FILENAME only refers to the batch file being created and its location, but sure i will add it for clarity. – sam c Nov 27 '18 at 12:57
  • 1
    @PatrickHonorez, actually it is not, it is about creation of a script for `ftp.exe`, which the OP erroneously mixes up with a Windows [tag:batch-file], which cannot work; instead of trying to call a batch-file that runs `ftp.exe`, the VBA script should directly run `ftp.exe` and pass the created script to it by its `-s` option; nevertheless I re-added the tag batch-file and added the [tag:ftp] tag... – aschipfl Nov 27 '18 at 13:00
  • If you don't need to persist the script, you should be able to just use `WScript.Shell` in interactive mode instead. You'll find a sample using putty [here](https://stackoverflow.com/a/41416923/4088852) that should be adaptable to using ftp instead. – Comintern Nov 27 '18 at 13:50

1 Answers1

2

What's going wrong is that the batch file first opens ftp in interactive mode and receives no input. If ftp woud terminate on its own, you'd notice that your batch file then goes on to execute the instructions username, password etc. Those aren't meant as instructions to Batch but to ftp, but that's not how Batch would see it.

To work around that, save all the ftp instructions to a second file, then call ftp in Batch with the second file as input parameter:

Dim FileNumber As Integer
Dim retVal As Variant
FileNumber = FreeFile

'create batch file
Open MY_FILENAME For Output As #FileNumber
Print #FileNumber, "FTP -s<your-filename-here> 00.0.000.000"
Close #FileNumber

Open FTP_INSTRUCTIONS For Output As #FileNumber
Print #FileNumber, "username"
Print #FileNumber, "password"
Print #FileNumber, "ascii crlf"
Print #FileNumber, "put  " myfile.txt; "  'location'"
Print #FileNumber, "exit"
Close #FileNumber

'run batch file
retVal = Shell(MY_FILENAME, vbNormalFocus)
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
steenbergh
  • 1,642
  • 3
  • 22
  • 40
  • thank you for a clear explanation of my issue. Just to confirm there is no way that you know of to have it run from 1 file? – sam c Nov 27 '18 at 13:07
  • @samc no, I don't think there is. All you're trying to do doesn't fit in the cmd line paameters of `ftp`, so you need to use the second file. Depending on your environment, you might just be able to create 1 instrcutinsfile, then call `Shell('ftp -s:')` instead of the Batch file. – steenbergh Nov 27 '18 at 13:11
  • 2
    You don't need a batch file. All you should need is an ftp script file and have the VBA directly run ftp.exe with the script file as a parameter. – Squashman Nov 27 '18 at 13:31
  • @Squashman you are correct, i found the syntax to open FTP without using a batch file: Shell Environ("WINDIR") & "\System32\ftp.exe -n -s:" & – sam c Nov 28 '18 at 13:33