Listing available files and Downloading from FTP site

I'm working on a project where I need to download files from an FTP site for processing. Ideally, I'd like to get a list of the files into an array first, then skip over the files that begin with "AB" - but that would be a grace note.

The crux of my problem is the download portion.

I've been trying to use this code (set up to test code only), but keep getting a "Run-time error '429' ActiveX component can't create object" error.

[vba]
Sub ftptest()
Dim DidIGetIt As Boolean
DownloadFile "ftp://aValidFTPsite", "username", "password", _
"I want my sample file.txt", _
"C:\Temp\DOWNLOADED SampleFile.txt"
MsgBox DidIGetIt
End Sub

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


Dim FTP as Inet
Set FTP = New Inet
With FTP
.Protocol = icFTP
.RemoteHost = HostName
.UserName = UserName
.Password = Password
.Execute .URL, "Get " + RemoteFileName + " " + LocalFileName
Do While .StillExecuting
DoEvents
Loop
DownloadFile = (.ResponseCode = 0)
End With
Set FTP = Nothing
End Function
[/vba]

I've got my references to Microsoft Internet Transfer Control 6.0 and ActiveX plugin set up, and no matter what I do, same problem. :dunno


Suggestions? Anyone? Beuhler? Beuhler?

PS: for some reason the VBA formatting thingie thinks its funny to stick colour references in lines 5 & 7 of the function, and muck about with the indents. Edit as needed, sorry & thanks.

Please try following macro.

[VBA]
Option Explicit

Sub ftptest()
MsgBox DownloadFile("ftp://aValidFTPsite", "username", "password", _
"I want my sample file.txt", _
"C:\Temp\DOWNLOADED SampleFile.txt")
End Sub

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

Dim FTP As InetCtlsObjects.Inet
Set FTP = New InetCtlsObjects.Inet
With FTP
.URL = HostName
.Protocol = 2
.UserName = UserName
.Password = Password
.Execute , "Get " + RemoteFileName + " " + LocalFileName
Do While .StillExecuting
DoEvents
Loop
DownloadFile = .ResponseInfo
End With
Set FTP = Nothing
End Function
[/VBA]

Suat

It choked out on the line "Set FTP = New InetCtlsObjects.Inet", Error msg:
"Run-time error '429' ActiveX component can't create object."

I've got the following apparently relevant References checked (I added a couple trying to get it to work):

• MS Internet transfer Control 6.0 (SP4)
• ActiveEx type library
• MS ActiveX Data Objects 2.7 Library
• MS ActiveX Data Objects Recordset 2.7 Library

Along with a number of others of course... which References are you running with? I have a feeling that that's the problem... but I've been known to be wrong before (just ask Mrs Wizard).

I appreciate your help...

Please see my references in attachment.

I don't think it is about a missing reference because you have MS Internet Transfer Control 6.0 (which should be MSINET.ocx as in my references).

Can you upload your excel file so I can run it in my PC to see what's happening ?

Suat

File as requested.
MS Internet Transfer Control 6.0 reference confirmed as MSINET.ocx.

Actually attached project worked perfect by giving my FTP server information.

The only difference is that your MS Internet transfer Control comes in SP4 but mine SP6. I am not sure but may be your ocx file (MSINET.ocx) somehow corrupted or need to re-register it.

Sorry, not much help.

Suat

I am having the same error trying to use almost identical code in an ftp application which uses the Inet class without attaching it to a form. It works on one machine but not the other. Dependency Walker on "MSINET.OCX" shows nothing different on the failing machine than it does on the working machine, namely only a delayed binding error for Java.dll. I have unregistered/deleted the control, and reinstalled it, but I still get the 429 error.

If I try to create the control on a form, I get "the control could not be created because it is not properly licensed". Other than Dependency Walker are there any other tools out there that could show why it is failing?

Even I am looking for exactly the same solution. Let us see how it works.

Regards,
Ajit

I found the problem with my particular case...

One possible problem encountered while trying to run VBA macros involves the Microsoft Internet Transfer control (MSINET.OCX) ActiveX control to perform the ftp ?put? from source to target machine. MSINET.OCX is a licensed ActiveX control and requires a license for it to run on any machine. If MSINET.OCX is embedded into an executable VBA form interface using the toolbox at build-time, on a machine with an MSINET.OCX license, then the license for the Form instance of MSINET.OCX is built into the executable at that same time. It can then be distributed to any other machine, with OR WITHOUT a license, and will not run into licensing problems since the license is already built in.

Unfortunately, if you try to create (an Inet object) using MSINET.OCX programmatically by creating the object at runtime, not at build time, like this:

Dim ftp as Inet
ftp = New Inet

on a machine with no license for MSINET.OCX, runtime VBA attempts to find a license on the target machine and when it does not find it then a run-time an error (429: Cannot create object) is generated.

Especially for non-development machines, it is difficult to predict if the license exists. If the target machine has ever had Visual Basic 6.0 installed on it, or certain other developer packages, then those packages will already have installed the license on that machine, so when the Inet control is created at runtime a license is found and no error will occur, leading to confusion as to why sometimes the installation works, and sometimes it does not. The license is the key. If the target machine has no license, installing Visual Basic Express (free download from Microsoft) will install the license on that machine.

Lack of a license can be verified by trying to create the Internet Transfer control (on the target machine) on a Form using the toolbox. Make sure you have the proper reference checked in the "Tools/References" box (Microsoft Internet Transfer Control 6.0). Then try to add the control to a form. If the target machine is missing the license, a ?there is no license for this control?? message is generated. You can also look at the registry to see if the license is there. The XP Registery license entry looks like this:

HKEY_CLASSES_ROOT \ Licenses

78E1BDD1-9941-11cf-9756-00AA00C00908