FTP using VBA

[Michaelpfreem]

Dear all,

I have some code to upload some data to an FTP site but it doesn't seem to work, everything I have seen on the net would suggest it should do what I want, but it doesn't appear so.

Basically I want to upload a file stored on my computer to an FTP site, my code is below:

Sub FTPupload()

Dim dRetVal As Variant
Dim aaa As Object, a As Variant
Dim bbb As Object, b As Variant

'write script.dat and upload.dat file
Set aaa = CreateObject("Scripting.FileSystemObject")
Set a = aaa.CreateTextFile("C:\folder\folder\script.dat", True)
a.writeline "username" 'username
a.writeline "password" 'password
a.writeline "put C:\folder\folder\filename.xls" 'file to be uploaded
a.writeline "quit"
a.Close

Set bbb = CreateObject("Scripting.FileSystemObject")
Set b = bbb.CreateTextFile("C:\folder\folder\upload.bat", True)
b.writeline "ftp -i -s:c:\folder\folder\script.dat ftp://00.000.000.00/FolderTest" 'the ftp site
b.Close

dRetVal = Shell("C:\folder\folder\upload.bat", 1) 'upload the file

End Sub

I am open to other suggestions on how to move files around from PC to FTP using excel VBA; but I am putting my code up here as a starting point.

Many thanks in advance,
Mike

 

[poolhall]

Hello Mike,

Is FolderTest, or whatever real name it is, your FTP home directory? If it's not, you need a cd command before the put command to navigate to the FTP folder you want to upload the file.

Also, I think, in your upload.bat the line should be this:
b.writeline "ftp -i -s:c:\folder\folder\script.dat 00.000.000.00"

i.e., without protocol and folder.

And, if long names are used in the path to your local file, you need to replace them with short names.