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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando Garcia SotoAuthor Commented:
Excelent
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.