Connecting to FTP with Excel?

[ossuary]

Hey folks,

I'm trying to automate some weekly reporting duties that involve downloading and processing files from another site.

Is there VBA code you can use to tell Excel to log onto an FTP server, and download / copy a file from it onto your computer?

If so, can someone please give me the syntax to do this?

Thanks in advance.

 

[ossuary]

Wow... this dropped to page 10 pretty quick!

Does anyone have any tips on this one for me?

 

[Ivan F Moala]

There are a number of ways to do this...... here is one

Note:
You will need to reference Microsoft Internet Transfer Control

Option Explicit

Sub FtpTest()
    MsgBox fnDownloadFile("ftp://yoursite", "username", "password", _
        "The name of your file", _
        "C:\The name of your file to save as")
End Sub
 
Function fnDownloadFile(ByVal strHostName As String, _
    ByVal strUserName As String, _
    ByVal strPassWord As String, _
    ByVal strRemoteFileName As String, _
    ByVal strLocalFileName As String) As String
     
    '// Set a reference to: Microsoft Internet Transfer Control
    '// This is the Msinet.ocx

    Dim FTP As Inet    'As InetCtlsObjects.Inet
    
    Set FTP = New Inet 'InetCtlsObjects.Inet
    
    On Error GoTo Errh
    With FTP
        .URL = strHostName
        .Protocol = 2
        .UserName = strUserName
        .Password = strPassWord
        .Execute , "Get " + strRemoteFileName + " " + strLocalFileName
        Do While .StillExecuting
            DoEvents
        Loop
        fnDownloadFile = .ResponseInfo
    End With
Xit:
    Set FTP = Nothing
    Exit Function

Errh:
    fnDownloadFile = "Error:-" & Err.Description
    Resume Xit
End Function

 

[ossuary]

Ivan,

Thanks for the help.

I am having some trouble getting this code to work. When I attempt to execute the download macro, I get the following error:

"Compile Error: user-defined type not defined"

This occurs on the line "Dim FTP AS Inet" from your function code.

I'm not entirely sure that I am using the code right - I'm not very well versed in functions. Where is the code supposed to go, and how exactly should I execute it?

Also, if I wanted to use some variables for the file names, how would do that in the function?

For example, let's say my file name is "05-12-2007 Weekly Report.xls", and next week it will be "05-19-2007 Weekly Report.xls", etc. Or that the username and password change from week to week. If I have that information stored in the file, how would I use variables in the macro to refer to those items instead of your "username", "password" part of the code?

 

[ossuary]

Hey guys... still hoping to get some help on this Excel / FTP code. I can't get the code that Ivan posted to work, and would also like to know the correct syntax to put some variables into the code, so I can change things like file names or passwords.

Thanks in advance.

 

[Ivan F Moala]

Ivan,

Thanks for the help.

I am having some trouble getting this code to work. When I attempt to execute the download macro, I get the following error:

"Compile Error: user-defined type not defined"

This occurs on the line "Dim FTP AS Inet" from your function code.

I'm not entirely sure that I am using the code right - I'm not very well versed in functions. Where is the code supposed to go, and how exactly should I execute it?

Also, if I wanted to use some variables for the file names, how would do that in the function?

For example, let's say my file name is "05-12-2007 Weekly Report.xls", and next week it will be "05-19-2007 Weekly Report.xls", etc. Or that the username and password change from week to week. If I have that information stored in the file, how would I use variables in the macro to refer to those items instead of your "username", "password" part of the code?

You will need to reference Microsoft Internet Transfer Control

In the VBA Editor > Tools > refrence ...
Look for the above reference

To refrence your variables set this up on your sheet and reference it from there

 

[greg_talbot]

Where Can I get this Control?

Hi, I'm writing an Access program to connect to an FTP server to upload a bunch of files I've created through Access, and I'm having no luck at all finding anything that I understand or that works at all. I am running Access 2000 on XP Pro and I ran across this posting. I went to VBA-Tools-References and I don't have the Microsoft Internet Transfer Control. I have searched my system for the ocx file and the internet for downloads, with no luck. As far as I can tell my MS Office suite is up-to-date with SP3. I would love to resolve this ftp connection issue, but I can't get the Shell to work, and frankly, that's not as slick as I know it could be. Do you know where I can get the Internet Transfer Control so I can reference it to try to use your code? Thanks!

 

[Jonmo1]

Re: Where Can I get this Control?

Hi Ivan,

Hatman directed me to this thread from this thread http://www.mrexcel.com/forum/showthread.php?t=293185&page=2 for help downloadning from an FTP Folder.

But I get an error

Runtime error 429
ActiveX Component Can't Create Object.

This is line highlighted.

Set FTP = New Inet 'InetCtlsObjects.Inet

I did get the Reference to Microsoft Internet Transfer Controls installed.

I have no idea how to proceed....thanks.