Trying to Download From FTP with VBA

[Jonmo1]

Hatman directed me to this post for help downloading from an FTP Site.
http://www.mrexcel.com/forum/showthread.php?t=261043&highlight=ftp

It gave me this code.
But it errors on the line

Set FTP = New Inet 'InetCtlsObjects.Inet

The error is
Runtime error 429
ActiveX component can't create object

I've set the reference to Microsoft Internet Transfer Control
And Ive run "regsvr32 msinet.ocx" - it says successfull.

Any help is appreciated.

Function fnDownloadFile(ByVal strHostName, _
    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

 

[BrianB]

It may be that you need to licence the control too by running the free MS download file VBUSC.EXE.
Please see my message further down in this posting with more details re .OCX installation :-

http://www.mrexcel.com/forum/showthread.php?t=289338

I would be interested to know if you get this to work because the inference is that it checks to see if the Visual Basic *application* (VB - not VBA) is present on the machine.

 

[Jonmo1]

Thanks for trying Brian. But It still doesn't work. The VBUSC.EXE ran successfully, and I did the REGSVR32 command - successfully. In inserted a userform, and set the reference to Micorosoft Internet Transfer Controls.

Still no joy, same error.

But I have found another way to do it. Using FTP.EXE directly in a VBA Shell command. That is working well for me.

 

[BrianB]

Would you please let us know your solution. I am sure that this is a common problem

 

[Jonmo1]

I create a Dos Batch File that does the FTP commands directly.

so I made some code that loops through my range of FileNames needed to download, and creates a new xl sheet with the FTP commands going from A1 to A2 to A3 etc...

Then it saves that as a TEXT file with ftpfile.BAT as the filename..

Then runs the batch file from a shell command.

Private Sub DownloadPose()

Dim x, LR, MyRow As Long
Dim CS, FtpFile As Workbook
Dim PosePath, FtpFilePath As String
Dim FileRange As Range
Dim item

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

'These 2 paths must conform to the Dos format C:\docume~1 - Truncated to 8 characters
PosePath = "C:\Path"  '  Where you want to PUT the files downloaded"
FtpFilePath = "C:\Path"   '  Where you want to put the FTP Batch File"

Set CS = Workbooks("completion_sheet.xls") ' My Workbook's name

'This line has nothing to do with the FTP.  This is part of my Dashboard
'to tell me files have been downloaded
MyRow = WorksheetFunction.Match(CDbl(Date), Sheets("This Month").Range("A:A"), 0)

Workbooks.Add
Set FtpFile = ActiveWorkbook
FtpFile.Sheets("Sheet1").Range("A1") = "Open ftppath.net"  '  URL to FTP Folder
FtpFile.Sheets("Sheet1").Range("A2") = "UserName"
FtpFile.Sheets("Sheet1").Range("A3") = "Password"
FtpFile.Sheets("Sheet1").Range("A4") = "lcd " & PosePath ' Changes Directory for FTP output

'My Range of Filenames to download
Set FileRange = CS.Sheets("FileDownloader").Range("F14:G20")

For Each item In FileRange
    LR = FtpFile.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
    If item.Value <> "No File To Download" Then

        ' My Dashboard gets updated to say a file was downloaded - not part of the FTP
        CS.Sheets("This Month").Range("F" & 40).Value = "Ready"

        FtpFile.Sheets("Sheet1").Cells(LR, "A").Value = "Get " & item.Value
    End If
Next item

LR = FtpFile.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
FtpFile.Sheets("Sheet1").Cells(LR, "A") = "Close"
FtpFile.Sheets("Sheet1").Cells(LR + 1, "A") = "Bye"
FtpFile.Sheets("Sheet1").Cells(LR + 2, "A") = "Quit"

ActiveWorkbook.SaveAs FtpFilePath & "ftpfile.bat", xlTextMSDOS
ActiveWorkbook.Close

x = Shell("ftp.exe -s:" & FtpFilePath & "ftpfile.bat", vbHide)

'this is a macro I have that resets application settings
Reset

End Sub

the resulting BATCH file ends up looking like this

Open FTPPATH.net
UserName
Password
lcd C:\PathToPutDownloadedFiles
Get Filename1
Get Filename2
Get Filename3
Close
Bye
Quit

 

[jojo1]

Hi , i used these following codes to ftp an excel file. I want it to FTP and overwrite the file test1.xlsx even if the file already exist in folder 'report'.

Error message keep popping up "file already exist". Kindly assist.

Sub FtpTest1()
    MsgBox fnDownloadFile("host", "user", "password", _
        "/server/folder/test1.xlsx", _
        "C:\Users\jojo\Desktop\report\test1.xlsx")
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

 

[BrianB]

How about deleting the file first :

Kill "filename"

 

[jojo1]

How about deleting the file first :

Kill "filename"

Ok , I did just that. However when I used the file in another PC .
Line

    Set FTP = New Inet

Produces error 429 "ActiveX component can't create Object"

Kindly advise. Been running around for the whole day.

 

[BrianB]

Sorry, no idea. I could be anything, the simplest being a slow server. I don't think its anything to do with the change. You could try checking that VB editor Tools\References are the same on both computers.

 

[jojo1]

I resolved the error Installed Visual Studio Express 2008 . Et voila, the problem gone