CODEWITHSUNDEEP
excelvbabatch-fileftp
sam c
sam c

Reputation: 149

Automate FTP upload with VBA

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?

Upvotes: 3

Views: 4172

Answers (1)

steenbergh
steenbergh

Reputation: 1771

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)

Upvotes: 2

Related Questions