Upload via SFTP using Excel VBA code

Question

I have tried to use the following code, which I found in a previous SO question, to upload a generated .php file to my shared server via FTP.

I am using Excel 2016 on Windows 10, and trying to upload to a server with 1and1.

Public Sub FtpSend()

Dim vPath As String
Dim vFile As String
Dim vFTPServ As String
Dim lInt_FreeFile01 As Integer

vPath = Environ("USERPROFILE") & "\Documents\UploadFiles\"
vFile = "rd1.php"
vFTPServ = "domain-name.co.uk"

lInt_FreeFile01 = FreeFile

'Mounting file command for ftp.exe
Open vPath & "\FtpComm.txt" For Output As #lInt_FreeFile01
    Print #lInt_FreeFile01, "domain-name.co.uk"
    Print #lInt_FreeFile01, "username"
    Print #lInt_FreeFile01, "password"
    Print #lInt_FreeFile01, "/test/"
    Print #lInt_FreeFile01, "ascii"
    Print #lInt_FreeFile01, "send " & Environ("USERPROFILE") & "\Documents\UploadFiles\" & "rd1.php"

    Print #lInt_FreeFile01, "bye"

Close #lInt_FreeFile01

Shell "ftp -n -i -g -s:" & vPath & "FtpComm.txt " & vFTPServ, vbNormalNoFocus

SetAttr vPath & "FtpComm.txt", vbNormal
Kill vPath & "\FtpComm.txt"

End Sub

When I run the macro, there is no feedback as to what has happened, but the file has not been transferred into the /test/ directory on the server. I've tried a number of permutations, but I can't get it to work.

Any advice on how I can get this to work?

On another note, I also tried the code here, but couldn't get this to work either...

I have previously used FileZilla to transfer files via FTP, but this Excel workbook is going to be handed out to a number of team members and the ability to upload using Excel VBA code is an important part of streamlining the workflow.

Tim · Accepted Answer · 2016-10-13 20:55:25Z

0

Change this line

Print #lInt_FreeFile01, "/test/"

to

Print #lInt_FreeFile01, "cd test"

or perhaps

Print #lInt_FreeFile01, "cd /test"

If you need to set the active directory relative to root.

If that doesn't work, then your issue is in the path you are copying from:

Environ("USERPROFILE") & "\Documents\UploadFiles\" & "rd1.php"

because the rest of your FTP Commands file looks legit.

answered Oct 13, 2016 at 20:55

Tim

2,9123 gold badges30 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Andrew Abbott Over a year ago

Made a few changes, including changing the directory path and your other suggestions, but it's still not working. The screen flickers briefly, but there's no other feedback and when I check the directory on the server, the file has not transferred. Has this code worked for you previously?

Andrew Abbott Over a year ago

Is it likely to make a difference that I am using SFTP instead of FTP, and if so can I specify this in the code?

Tim Over a year ago

sFTP makes a huge difference. Windows doesn't do sFTP natively. You will need to use WinSCP or PuTTY.

Andrew Abbott Over a year ago

Got it. I'll set aside some time to explore WinSCP before coming back to this. Thanks very much.

Tim Over a year ago

The PuTTY code is in this post btw: stackoverflow.com/questions/35457719/…

Collectives™ on Stack Overflow

Upload via SFTP using Excel VBA code

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related