0

I created a test file on my D drive. My goal is to upload it to my website from a VBA script in Excel. When I run the .bat file it hangs during the put. What am I doing wrong? I commented out the / line because that was giving me an error.

Reply when running upload.bat from command prompt

D:\>upload.bat

D:\>ftp -i -s:d:\script.dat domain.com
Connected to domain.com.
220 *** FTP Server Ready
200 UTF8 set to on
User (domain.com:(none)):
331 Password required for username

230 User username logged in
ftp> put d:\test.txt
200 PORT command successful
425 Unable to build data connection: Connection timed out
ftp> quit
221 Goodbye.
enter code here

Sub ftp()

Dim fs As Object

Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("d:\script.dat", True)
    a.writeline "username" 'username
    a.writeline "password" 'password
    'a.writeline "\" 'directory on FTP site
    a.writeline "put d:\test.txt" 'file to be uploaded
    a.writeline "quit"
    a.Close

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile("d:\upload.bat", True)
    a.writeline "ftp -i -s:d:\script.dat domain.com" 'the ftp site
    a.Close

    dRetVal = Shell("d:\upload.bat", 0) 'upload the file

    Application.ScreenUpdating = True

End Sub
GaryC
  • 83
  • 1
  • 5
  • I was able to run your script with no error. What error are you getting ? By the way, you should always use `Option Explicit`, it helps a lot. – FAB May 26 '19 at 00:27
  • Thanks DarXyde. The problem I have is that after I run the macro, I go to my command prompt, I run "upload.bat," and it goes through the FTP results that are above my code. When "200 Port command successful" appears, things seem to hang. After about a minute or so, it says "425 Unable to build data connection: Connection timed out" and then "quit." When I look at the directory on my website, the test.txt file is 0 bytes. There is nothing in it; just the name – GaryC May 26 '19 at 00:31
  • To be honest I came along because of the vba tag. As far as I can tell, the above code works.. as for what it produces, is a different story. Are those commands ran individually in command prompt getting the result you need? Also, this might help [How to ftp with a batch file](https://stackoverflow.com/questions/16158138/how-to-ftp-with-a-batch-file) – FAB May 26 '19 at 00:38

1 Answers1

1

200 PORT command successful
425 Unable to build data connection: Connection timed out

This happens usually if your are behind some firewall or device doing NAT (i.e. typical SoHo router). FTP requires a separat data connection and in active mode (as you use here) the server tries to connect to the client - which fill fail in the given scenarios either with a connection reset or with a timeout (as in your case).

You'd better use passive mode where the client will try to connect to the server for the data connections instead. Unfortunately, the builtin command line client in Windows does not seem to support passive mode so you would need to use a different client.

Steffen Ullrich
  • 114,247
  • 10
  • 131
  • 172
  • Steffen, that makes a lot of sense. I'm not sure why I didn't notice that because it was pretty clear I was not getting 2 way communications. I guess I was so fixed on looking elsewhere, that I didn't pick up on that. Okay, having said that, this will be installed on customers' machines, and I do want it to communicate back to me somehow. I tried e-mail, but the firewall blocked that. This would require another client installed. I'd like it relatively seamless. What would you recommend for getting communication back to me from the clients? If it is still FTP, which do you recommend? – GaryC May 26 '19 at 12:35
  • @GaryC: The easiest thing is likely to use some HTTP upload and not FTP. VB should be able to [do that natively](https://learn.microsoft.com/en-us/dotnet/visual-basic/developing-apps/programming/computer-resources/how-to-upload-a-file). Of course the needs some HTTP server with an appropriate upload app at your end but apart from that it is much less pain than FTP since this is the last thing firewalls would block. As for recommending some FTP client for windows - this is a different question, off-topic and I also have no idea. – Steffen Ullrich May 26 '19 at 14:06
  • Thank you Steffen. I will look into that. I tried to up vote your reply, but since I only have a reputation of 10 (and not 15), I was not able to do so. Thank you though. I do appreciate it. – GaryC May 26 '19 at 14:30