macro for upload file to a ftp server

Hello

I have a macro in Excel that saves data in a .txt file but I want to send that data to an FTP server, I found a code but when using it it worked only once, I tried to work again but I have not succeeded, the code is the following:

Private Sub Workbook_Open ()
Set fs = CreateObject ("Scripting.FileSystemObject")
Set a = fs.CreateTextFile ("D: \ mom.txt", True)
With CreateObject ("WScript.Network")
a.WriteLine "Environment variables:" & vbCr
a.WriteLine "Device name:" & Environ ("computername")
a.WriteLine "Domain name:" & Environ ("userdomain")
a.WriteLine "Username:" & Environ ("username")
a.Close
End With
End Sub

Sub TestFTPUpload ()
On Error GoTo ErrHandler
Dim objFTP As InetTransferLib.FTP
Const conTARGET = "ftp://ftp.192.168.57.136.com"

  Set objFTP = New InetTransferLib.FTP
  With objFTP
    .FtpURL = conTARGET
    .SourceFile = "D: \ mom.txt"
    .DestinationFile = "/mother.txt"
    If Not .IsConnected Then .DialDefaultNumber
    .ConnectToFTPHost "megatron", "password"
    .UploadFileToFTPServer
  End With
End Sub

The server is a VM server Win2k8 FTP Server by IIS, they could help me where the error is?
Fernando Garcia SotoAsked:
Who is Participating?
 
KimputerCommented:
Most probably, you broke the whole macro (especially if there's no FTP traffic). To to use the original code, and only fix the server line I told you about in the first post.
Also check in debugging mode line by line to see where it goes wrong.
0
 
KimputerCommented:
Assuming this is your server IP: 192.168.57.136
Adjust this:

Const conTARGET = "ftp://192.168.57.136"
0
 
Fernando Garcia SotoAuthor Commented:
Make the change and it does not work, also change in each line:

Dim objFTP As InetTransferLib.FTP
Set objFTP = New InetTransferLib.FTP

for this

Dim objFTP As .FTP
Set objFTP = .FTP

it still does not work.  I attach the file in case it is necessary
testing_macro.xls
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Fernando Garcia SotoAuthor Commented:
I dont know if there is any connection to the FTP server that rejects a new connection and does not allow to load the file
0
 
Fernando Garcia SotoAuthor Commented:
These are the references that are active
0
 
KimputerCommented:
If the FTP server is rejecting it, then check what the error was (usually wrong username/password combination)
0
 
Fernando Garcia SotoAuthor Commented:
By command line I upload files to the server using the username and password, but through the code does not work
0
 
KimputerCommented:
Debug step by step, have Wireshark listening on port 21 to check what else is going on.
0
 
Fernando Garcia SotoAuthor Commented:
Run wireshark and place it in listening to the IP of the vm (192.168.57.136), the first time I ran the macro it worked and it was recorded by wireshark but the second time it runs it only runs the macro until the creation of the file but the FTP upload is not done and the connection is not recorded by wireshark.
0
 
KimputerCommented:
It also didn't work after you fully closed all Excel instances (and then tested again)?
0
 
Fernando Garcia SotoAuthor Commented:
I have closed the document and I have opened it several times and the code is executed until the creation of the file but the connection to FTP is not made
0
 
KimputerCommented:
So if you change source and remote file name, it will upload one time successfully again?
0
 
Fernando Garcia SotoAuthor Commented:
Still not uploading the file to FTP
0
 
Fernando Garcia SotoAuthor Commented:
Excelent
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.