VBA - Download from FTP

[mwheatley]

Hi

I have the code below attached to a button in a macro. This is the code in complete and I have been using it to test taking the information from a file on my computer. This works fine, it all plugs in to the right place - no issue.

Now that file is on my ftp server which is user and password protected. *I have literally no idea how to go about downloading or (preferably) opening this file from the ftp*

Could anyone help me out with how to download my file?

Sub ImportTextFile()

Application.ScreenUpdating = False


    Password = "ABC"
    UserName = "ABC"
    Filename = "ABC"
    Serverlocation = "ftp:www.abc.com\"


     INSERT CODE TO DOWNLOAD FILE FROM FTP SERVER HERE






myTextFile = Workbook.Open(-------)


Dim lastCol As Long
lastCol = ThisWorkbook.Sheets("Continuity").Cells(4, Columns.Count).End(xlToLeft).Column
 
   myTextFile.Sheets(1).Range("A4:A11").Copy
   ThisWorkbook.Sheets("Continuity").Cells(4, lastCol + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    
    If myTextFile.Sheets(1).Range("A5") = ThisWorkbook.Sheets("Continuity").Cells(5, lastCol) Then
    ThisWorkbook.Sheets("Continuity").Cells(4, lastCol + 1).ClearContents
    ThisWorkbook.Sheets("Continuity").Cells(5, lastCol + 1).ClearContents
    ThisWorkbook.Sheets("Continuity").Cells(6, lastCol + 1).ClearContents
    ThisWorkbook.Sheets("Continuity").Cells(7, lastCol + 1).ClearContents
    ThisWorkbook.Sheets("Continuity").Cells(8, lastCol + 1).ClearContents
    ThisWorkbook.Sheets("Continuity").Cells(9, lastCol + 1).ClearContents
    ThisWorkbook.Sheets("Continuity").Cells(10, lastCol + 1).ClearContents
    ThisWorkbook.Sheets("Continuity").Cells(11, lastCol + 1).ClearContents
    
     
    End If


myTextFile.Close = False
Application.ScreenUpdating = True


End Function

 

[mwheatley]

Wow, these disappear fast. Bump