Basic FTP upload problems please help, kinda urgent?

[j.smith1981]

Hi there,

I am having some problems with a very basic FTP macro I am trying to create.

I firstly went to this forum:
http://www.ozgrid.com/forum/showthre...985#post303985

I then modified that code to:
[vba]Sub ftpUpload()
Dim fs As Variant
Dim FTPScript As Variant

Cells(21, 6).Select
localFile = ActiveCell.Value

Set fs = CreateObject("Scripting.FileSystemObject")
Set FTPScript = fs.CreateTextFile("C:\FTPXfer.txt", True)
With FTPScript
.Writeline ("OPEN 127.0.0.1")
.Writeline ("USER jeremy s56pj989")
.Writeline ("ASCII") 'Either BINARY or ASCII (just choose when on Linux!
.Writeline ("CD ../dest")
.Writeline ("LCD " & ThisWorkbook.Path & "\")
.Writeline ("PUT 14-09_04-03-2009_UPLOADproducts.csv 14-09_04-03-2009_UPLOADproductsUPLOADED.csv")
'.Writeline ("BYE")
'.Close
End With
Call Shell("C:\WINDOWS\System32\ftp.exe -ns:c:\FTPXfer.txt", vbMaximizedFocus) ' or MinimizeFocus

'NEED TO MAKE COMMAND TO REMOVE 'c:\FTPXfer.txt'

End Sub[/vba]

But it appears to be uploading a blank CSV file (even though there's data in the local directory one) is there anyway of getting it working so that the uploaded CSV to my server (at the moment its localhost or 127.0.0.1 same as localhost) but it appears to be uploading a blank CSV file, why is this and what is the solution, does anyone know?

BTW activecell.value is the file name of the last CSV upload Im trying to make it so we can do automated backups of files by date and time of the file being processed from our suppliers site.

Thanks,
Jez.

[Ago]

this is how i do it:

[VBA]
Dim bat As String

bat = "J:\Documents and Settings\Andreas\My Documents\bat.bat"
Shell bat, vbHide
[/VBA]

bat.bat

ftp -s:bat.txt ftp.scorpionshops.com

bat.txt

<username>
<password>
binary
cd <directory>
put "J:\Documents and Settings\Andreas\Desktop\file.xls"
close
quit

works perfect.

[j.smith1981]

this is how i do it:

[vba]
Dim bat As String

bat = "J:\Documents and Settings\Andreas\My Documents\bat.bat"
Shell bat, vbHide
[/vba]
bat.bat

ftp -s:bat.txt ftp.scorpionshops.com

bat.txt

<username>
<password>
binary
cd <directory>
put "J:\Documents and Settings\Andreas\Desktop\file.xls"
close
quit

works perfect.

Ah thanks soo much for your reply, will give that a go and get back to ya if it works for me haha.

Thanks again,
Jez

[Ago]

no problems.

i think this way is better since you dont have to send the password along with the file you are uploading. (if you are uploading the xls-file)

i have mine set up so when i close the file it sends creates a copy on my webpage.
and if you share it with others that cant send it (dont have the bat-file) you can do like this:

[VBA]
Private Sub Workbook_BeforeClose()
dim bat as string
dim ans as vbmsgboxresult

If FileThere("J:\Documents and Settings\Andreas\My Documents\bat.bat") Then
Ans = MsgBox("Upload file?", vbYesNo)
If Ans = vbYes Then
On Error Resume Next
bat = "J:\Documents and Settings\Andreas\My Documents\bat.bat"
Shell bat, vbHide
End If
End If
end sub
[/VBA]

in this way people that dont have the batfile wont get the question

[j.smith1981]

I seem to be unable to login to my ftp account on my local machine.

I have put the following credentials in:

VB code:
[VBA]
Public Sub ftpUpload2()

Dim bat As String

Shell ("C:\WINDOWS\system32\cmd.exe /k C:\ftp.bat")
[/VBA]
I am not sure the terminology of this but i presumed through looking at your above example it wasnt calling the command prompt, this was copied partially from when I setup an automated run of SC1.exe with borland C++ compiler and I knew it would work.

(I believe this to parse the ftp.bat commands to the command prompt, thats what I believe to be the terminology but dont quote me on that)

ftp.bat script

ftp -s:C:\ftp.txt 127.0.0.1

as you can see nothing got changed here although the server address changed obviously.

ftp.txt

USER jeremy s56pj989
BINARY
CD ../dest
put "C:\Documents and Settings\jeremy smith\Desktop\xCartProdUPDATESproject\13-13_04-03-2009_UPLOADproducts.csv"
close
quit

This is where I am getting confused, I tried putting in the format of your .txt file and it didnt seem to work, so I attempted the above but its still rejecting the password, it is connecting to '127.0.0.1' and accepting my username but not the password though as you can see its clearly stated.

Is there anyone who can help me please?

Thanks again this is pritty neat stuff I am learning here.

Jez.

[j.smith1981]

Scratch that error dunno why I was putting in USER but its working now many thanks for that ace post.

Now im going to attempt a 3rd version of my upload macro, to make it work when you transfer over the file to another user (the whole point of my macro's).

Any suggestions for that would be greatly appreciated but shall have a go myself, and will attempt your 2nd suggestion so thanks for that in advance.

Jez

[Ago]

you should not have to call cmd.exe but if it works the way you wrote, just leave it.

Now im going to attempt a 3rd version of my upload macro, to make it work when you transfer over the file to another user (the whole point of my macro's).

meaning: if you send the excelfile to someone else they can upload your file (from your computer) to the server?
or another file somewhere else?
im confused. tell me what you need and im sure we can make it work.

[j.smith1981]

you should not have to call cmd.exe but if it works the way you wrote, just leave it.



meaning: if you send the excelfile to someone else they can upload your file (from your computer) to the server?
or another file somewhere else?
im confused. tell me what you need and im sure we can make it work.

I will explain fully what I am doing and what I have got working.

I have written a macro that downloads a CSV file from our suppliers ftp server.

Then the macro performs some reorganising of the data so that it is compatible with our ecommerce sites import function/facility.

I then did the above posts macro to make a upload to a localhost ftp server using Filezilla (from XAMPP) due to my home ftp server being down.

That then worked.

But when moved over the instructions to make an ftp connection with my Linux Fedora 10 server with proftpd setup in a mySQL database as virtual users can be added and removed freely.

It appears to upload a file called 'AND' with just the location of the file and not the file itself.

Here is my code for it

VBA ftp upload code:
[VBA]Public Sub ftpUpload2()

Dim bat As String

Shell ("C:\WINDOWS\system32\cmd.exe /k C:\ftp.bat")

End Sub[/VBA]

ftp.bat script:

ftp -s:C:\ftp.txt

ftp.txt:

OPEN 82.30.227.113
exampleuser
secret
BINARY
CD ../upload/
PUT C:\Documents and Settings\jeremy smith\Desktop\xCartProdUPDATESproject\13-13_04-03-2009_UPLOADproducts.csv
CLOSE
QUIT

when i run the macro the connection seems to be fine but when the status of the bytes seemed a bit weird like had completely changed and comes up with the following output (in its entirity, excuse spelling lol):

C:\Documents and Settings\jeremy smith\Desktop\xCartProdUPDATESproject>ftp -s:C:
\ftp.txt
ftp> OPEN 82.30.227.113
Connected to 82.30.227.113.
220 FTP Server ready.
User (82.30.227.113none)):
331 Password required for exampleuser

230 User exampleuser logged in.
ftp> ASCII
200 Type set to A
ftp> CD ../upload/
250 CWD command successful
ftp> PUT C:\Documents and Settings\jeremy smith\Desktop\xCartProdUPDATESproject\
13-13_04-03-2009_UPLOADproducts.csv
200 PORT command successful
150 Opening ASCII mode data connection for and
226 Transfer complete
ftp: 76 bytes sent in 0.00Seconds 76000.00Kbytes/sec.
ftp> CLOSE
221 Goodbye.
ftp> QUIT

C:\Documents and Settings\jeremy smith\Desktop\xCartProdUPDATESproject>

I did change the ftp.txt file from BINARY to ASCII to see what that would do if it would work, no success.

As I said im running proftp and not vsftp on my fedora server.

This is the contents of what I get uploaded on my Linux server at:

/home/www.example.com/upload/

a file named: 'and'

no file extension just a unassociated file:

with the following contents when i open it in WinCSP:

'"Complete" and Settings\jeremy smith\Desktop\xCartProdUPDATESproject\.out'

thats it basically.

Is there something I am doing wrong or completely missing her im clueless as to what to do now as I have come this far with this macro really want to get it working.

Thanks ever so much,
Jeremy.

[j.smith1981]

the only thing I can think of is that Linux doesnt like the 'and' in the Documents and settings folder and then it just carrys on putting the rest of it into the and file, but not too sure. Doesnt make allot of sense to me.

[Kenneth Hobs]

Maybe:
[VBA]PUT "C:/Documents and Settings/jeremy smith/Desktop/xCartProdUPDATESproject/13-13_04-03-2009_UPLOADproducts.csv"[/VBA]

[GreenTree]

From having played around recently with something similar, I suspect that Kenneth is right, since DOS just plain doesn't like spaces (i.e. Documents _ and _ Settings, jeremy _ smith) in the command lines. Quotes make thing work much better.

It's an entirely different approach to FTP, and you'll have to tweak it for sending instead of receiving, but the code in the 3rd post of http://www.vbaexpress.com/forum/showthread.php?t=16836 has a working FTP solution that never touches DOS. May be worth investigating if the quotes don't resolve things completely.

G.T.

[Kenneth Hobs]

The problem is two-fold I suspect. Quotes with DOS and slash with the ftp server. Most ftp servers are Unix based so they like forward slashes.

Test your BAT file manually first.

IF the DOS method works for you, you can always make Excel write the BAT file similar to what the DailyDoseOfExcel thread that GreenTree referenced does.