Link to home
Create AccountLog in
Avatar of jlove88
jlove88Flag for United States of America

asked on

Upload a file to an ftp server using VBA in Excel

I have searched the KB for an answer but cannot quite understand the responses.  I have a macro (named Output) written that will create a csv file from an Excel workbook and save to a directory The path to this directory is located in a cell in one of the w'book tabs which I have named Setup Information.  In this same tab there is a cell that contains the url to a secure ftp server and cells with the login credentials (username and password).  I want to add code to the above mentioned macro to do the following:
1) open the url the secure ftp site using IE (would also be useful to get the code for other browsers such as Chrome, Firefox, Safari)
2) login to the ftp server using the credentials located in the Setup Information tab
3) upload the csv file from the directory specified in the Setup Information tab
4) logout the ftp server and close the browser window.
 I know this can be done manually quite easily, however having the coding will reduce user error and the number of tasks they would perform.
Thanks in anticipation
Avatar of SiddharthRout
SiddharthRout
Flag of India image

1) open the url the secure ftp site using IE (would also be useful to get the code for other browsers such as Chrome, Firefox, Safari)

You don't need a browser to upload a file to ftp...

Here is my suggestion...

(In fact I am preparing a small tutorial for you :)

Give me few minutes...

Sid
Avatar of jlove88

ASKER

Thank you
Ok Follow these steps...

Create a userform and then right click the toolbox to add the Common dialog and the inet control. See picture attached. Once done create 3 textboxes, 1 listbox and 3 command buttons as shown in the picture.

Sid

ps: Continuing in the next post...
1.GIF
2.GIF
Once your form has been set up. Use the following code

The listbox is required in case you want to upload multiple files to ftp.

The button below the listbox should have the following code

Private Sub CommandButton2_Click()
    On Error GoTo cancel_error
    Dim strPath As String
    With CommonDialog1
        '~~> Set the Title of the Dialog Box
        .DialogTitle = "Select Files for Upload"
        '~~> Set the Initial directory
        .InitDir = "C:\"
        '~~> Set the Filter for the files that you want to open
        .Filter = "Text Files (*.csv)|*.csv"
        '~~> Displays the Open Dialog Box
        .ShowOpen
    End With
    strPath = CommonDialog1.fileName
    ListBox1.AddItem strPath
    Exit Sub

cancel_error:
     If Err.Number <> vbCancel Then
          MsgBox Err.Description
     End If
End Sub

Open in new window

The button to connect to ftp should have the following code

Private Sub CommandButton1_Click()
    On Error GoTo FTPError
    
    Inet1.URL = Trim(Text1.Text)
    Inet1.UserName = Trim(Text2.Text)
    Inet1.Password = Trim(Text3.Text)
    Inet1.Execute , "", "POST", ""
    
FTPError:
    Select Case Err.Number
    Case 35754
        MsgBox Err.Description
    Case Default
        MsgBox "Connected Successfully."
    End Select
End Sub

Open in new window

The button to upload the file should have the following code

Private Sub CommandButton3_Click()
    Dim localfile As String, remoteFile As String, fileName As String
    Dim FilePath, fileid As String, seperatorPos, s1 As Integer
    Dim str As String
    
    For k = 0 To ListBox1.ListCount - 1
        localfile = ListBox1.List(k)
        remoteFile = GetFileName(localfile)
        Inet1.Execute , "PUT """ & localfile & """ """ & remoteFile & """"
        While Inet1.StillExecuting
            DoEvents
        Wend
    Next k
End Sub

Open in new window

And you are done :)

To test follow these steps

1) Ensure you fill the relevant details in the textboxes
2) You select the files you want to upload
3) You press the "Connect button". Once you are connected, it will give you a message box that you have connected successfully.
4) The moment you get the above message. Click on Upload files :)

Let me know if you get any errors :)

Sid
There are some variables which I have declared but not used... Please ignore them...

Sid
Avatar of jlove88

ASKER

Sid.  I have run into a problem at first base.  The Microsoft Internet Transfer Control is not available as an option under Additional Controls. Suggestion?
Which office version are you using?

Sid
Avatar of jlove88

ASKER

Office 2007
ASKER CERTIFIED SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jlove88

ASKER

For this solution to work, does msinet.ocx have to be available in the Windows\System32 folder on every PC where the user uses my workbook?
Yes.

Sid
Avatar of jlove88

ASKER

Sid, thank you for your efforts.  This solution looks very good to me and simple for someone to use, however, this Excel workbook or template as it is called, is currently used by multiple clients and multiple users that send information to our organization in a format that we require.  Currently the output is sent as an email attachment.  I wanted to add the code to send this to an ftp server to reduce the email traffic and allow for some automation.  To require any person who needs to use the template to have msinet.ocx installed on their system will be problematic.  Is there an alternative?  The template has to be ready to use without any program installation required.

Thanks

Jonathan    
Do you want a vba Solution only or a VB6/VB.Net solution will also do? We can pack it up in an installable file and then the end user can install the program?

Sid
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jlove88

ASKER

It is the VBA solution only.  I will try the link you sent me.  Thanks
Avatar of jlove88

ASKER

I wnr to the VBA solution link, copied and pasted the code, changing the relevant items within the code.  I executued the code but nothing happened :(  I am not even logging into the company secure ftp server.
SOLUTION
Avatar of SysExpert
SysExpert
Flag of Israel image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of jlove88

ASKER

While the solution is good I am limited in how I can use it.  Based on what has been provided I think I can figure out the coding once I figure out how to access the ftp site.
I just saw this and have a solution that I use that may assist.  See here for details:

https://www.experts-exchange.com/questions/26849062/Upload-a-file-to-FTP-Server.html