Excel 2003 VBA Upload File via FTP

Posted on 2005-05-05
Last Modified: 2012-08-14
I have written an Excel 2003 VBA macro to save an Excel sheet as a CSV file locally.  I then want the macro to upload that CSV file to an FTP site using a hardcoded id & password.

I created a macro called mcrTestUploadControl.   This macro calls the custom function  UploadFile (pasted below).   I copied this function from a website:     The function uses an old ActiveX control,  msinet.ocx,  that is called the “Microsoft Internet Transfer Control“.  I have this old control on my home computer, but not on my work computer (Windows XP with Excel 2003).  

I can compile the macro and function on my home computer, but the function does not upload the file anyway.

Can anyone help me with an Excel 2003 macro to upload the CSV file via FTP?  In Excel 2003, VBA just does not recognize the Inet control.




Function UploadFile(ByVal HostName As String, _
    ByVal UserName As String, _
    ByVal Password As String, _
    ByVal LocalFileName As String, _
    ByVal RemoteFileName As String) As Boolean

    Dim FTP As Inet

    Set FTP = New Inet
    With FTP
        .Protocol = icFTP
        .RemoteHost = HostName
        .UserName = UserName
        .Password = Password
        .Execute .URL, "Put " + LocalFileName + " " + RemoteFileName
        Do While .StillExecuting
        UploadFile = (.ResponseCode = 0)
    End With
    Set FTP = Nothing
End Function

Question by:eguether
    LVL 30

    Expert Comment

    So the issue is that the control is not recognised at work, or the control doesn't work?

    Maybe you could use the DOS FTP command?
    LVL 7

    Expert Comment

    There's another example of using the Inet control (seems that a lot of people have used this, and had problems) here:;EN-US;q163653

    Looking at your syntax, I assume that you just left out where you are setting the .URL property?  If you wanted to try shelling out, you could use something like this:

    Author Comment

    I was able to make the FTP upload function work on my home computer, because it has the MSINET.ocx control.

    But I need a solution that does not require the MSINET.ocx, because none of the newer, XP computers here at work have that control.

    First, does anyone know what Microsoft implemented as a replacement or substitute or MSINET.ocx?

    Second, we have considered nmcdermaid's suggestion to use the DOS FTP command, but is there any way to use such a command in XP/Excel 2003 without needed a BAT file on the client?

    Third, to answer nmcdermaid's question:  My issue is how to get an Excel 2003 macro to upload the CSV file via FTP . . . without using the Inet control (msinet.ocx).

    Sorry for the confusion.


    Author Comment

    Perhaps my solution needs to start with the "WININET.DLL"  But I'm not a programmer, so I don't know how to use this DLL in Excel 2003 VBA to upload my file.

    There's an interesting website (URL below), but its FtpPutFile function has a Long Integer parameter for the FTP site.  I only know the FTP address as a URL!

    LVL 30

    Expert Comment

    To use this DLL, you go into VBA and add the DLL as a reference.

    You should install the DLL on your computer first.

    In answer to 'how to get an Excel 2003 macro to upload the CSV file via FTP . . . without using the Inet control (msinet.ocx).'

    Use windows FTP (assuming you have a modern version of windows)

    Go to the command line (Start/Run then type CMD)

    then type in FTP /? to get all of the command line switched for the windows FTP.
    LVL 7

    Accepted Solution

    ftp /? won't provide a list of supported switches (it thinks you're trying to connect to a host unfortunately).  You can type ftp, and you will enter the ftp console; from there, you can use '?' to ask for additional assistance.  The second link posted above:

    has a reference to what would be required to use ftp by shelling out.  You wouldn't require a batch file necessarily, and you could write the ftp script file to the machines by using the Scripting.FileSystemObject (among others).  I think the wininet.dll is the replacement you were looking for, though.  I'll take a look around here in a bit for specifics of the implementation.  Regards.
    LVL 30

    Expert Comment

    oops FTP -? will be a bit more helpful.

    Author Comment


    From my Excel 2003 VBA macro, I can now upload a file to an FTP site using the following  mcrFTPUploadTest  macro.  Of course, I have had to replace the IT and password below.  However, this only works because I have "hardcoded" the From or Source file, "C:\winzip.log", when I call the FtpPutFile function.

    Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
        (ByVal sAgent As String, ByVal lAccessType As Long, _
         ByVal sProxyName As String, _
         ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

    Private Declare Function InternetConnect Lib "wininet.dll" Alias _
        "InternetConnectA" _
        (ByVal hInternetSession As Long, ByVal sServerName As String, _
         ByVal nServerPort As Integer, ByVal sUsername As String, _
         ByVal sPassword As String, ByVal lService As Long, _
         ByVal lFlags As Long, ByVal lContext As Long) As Long

    Private Declare Function FtpPutFile Lib "wininet.dll" Alias _
        "FtpPutFileA" _
        (ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _
        ByVal lpszRemoteFile As String, ByVal dwFlags As Long, _
        ByVal dwContext As Long) As Boolean

    Sub mcrFTPUploadTest()
    ' Test the FTP Upload component with a "hard-coded" local file, for example, "c:\winzip.log"
    Dim lngINet
    Dim lngINetConn
    Dim blnRC
    lngINet = InternetOpen("MyFTP Control", 1, vbNullString, vbNullString, 0)
    lngINetConn = InternetConnect(lngINet, "", 0, _
        "ID", "PASSWORD", 1, 0, 0)
    blnRC = FtpPutFile(lngINetConn, "C:\winzip.log", "winzip.log", 1, 0)
    End Sub

    But my goal is to have another macro create a CSV file from the open worksheet.  Then have the macro pass in the new, open CSV file name as the From, or Source, file when I call the FtpPutFile function.  My problem is that the macro below completes without error -- but does not upload anything to the FTP site.  Can anyone help?

    Sub mcrExportToCSVFile()
    ' Set CurrentDate to Today's System Date
        Dim CurrentDate         As Date
        CurrentDate = Date
    ' Set CurrentDateText to Formatted Text String
        Dim CurrentDateText     As String
        CurrentDateText = UCase(Format(CStr(Date), "dd-mmm-yy"))
        Dim ProposedCSVFileName As String
        Dim DesiredCSVFileName  As String   ' CSV file name with directory
        Dim FileNameOnly        As String   ' CSV file name without directory
        ProposedCSVFileName = CurrentDateText & "A.csv"
    ' Prompt user to confirm or change proposed CSV file name
        DesiredCSVFileName = Application.GetSaveAsFilename(ProposedCSVFileName, fileFilter:="CSV (Comma delimited) (*.csv), *.csv")

        Continue = MsgBox("The CSV filename to be saved is: " & DesiredCSVFileName)
        If Continue = "7" Then
            Exit Sub
        End If
    ' Save the Data sheet as a CSV file name
        ActiveWorkbook.SaveAs Filename:=DesiredCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
    ' Determine CSV file name without directory
        FileNameOnly = ActiveWorkbook.Name
    ' Upload the CSV file to the FTP site
       Dim lngINet
       Dim lngINetConn
       Dim blnRC

       lngINet = InternetOpen("MyFTP Control", 1, vbNullString, vbNullString, 0)

       lngINetConn = InternetConnect(lngINet, "", 0, _
        "ID", "PASSWORD", 1, 0, 0)

       blnRC = FtpPutFile(lngINetConn, DesiredCSVFileName, FileNameOnly, 1, 0)
    ' Pause for 20 seconds
       newHour = Hour(Now())
       newMinute = Minute(Now())
       newSecond = Second(Now()) + 20
       waitTime = TimeSerial(newHour, newMinute, newSecond)
       Application.Wait waitTime

    End Sub

    LVL 30

    Expert Comment

    Does it create the CSV file?

    Whats with the 20 second pause?


    Author Comment

    I finally got it to work.  However, in order to make the  mcrExportToCSVFile  macro upload the CSV file, I had to modify it.  After I do the SaveAs to create the CSV file, I have to do a 2nd SaveAs to create a second "temp" CSV file, which becomes the current open file.  Then I can upload the first CSV file by passing in the filename as a variable.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now