Connecting to FTP from Excel to automate file shar

Question

I'm a beginner and new to Excel VBA, but I'm trying to automate some file sharing in FTP (WinSCP) by connecting to Excel and maybe creating a macro that will help. In FTP I went to Session > Generate Session URL/code > Script (script file) and the following code is there:

open ftp://myUsername:myPassword@theHostname/

# Your command 1
# Your command 2

exit

I'm assuming the open line would connect Excel to FTP. I'm referencing code from this site to put into the '# command' area: https://www.mrexcel.com/forum/excel-questions/261043-connecting-ftp-excel.html

open ftp://myUsername:myPassword@theHostname/

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

exit

I did as this site said to go to VBA Editor > Tools > reference and check off Microsoft Internet Control.

1) Am I using the code right? Did I place it in the right area (in the '# command' area)? And right now I put the entire code in a Command Button, but when I click it it just gives me a Syntax Error highlighting the first line: Private Sub CommandButton1_Click())

2) Do I leave the Msgbox on the 3rd line as is to wait for user input or do I fill out with my username/password/hostname? (I'm not very good with functions in VBA yet) If I do fill it out in the code, what do I put for the "yoursite" value since I'm not accessing a website?

I'm sorry I'm so confused :( Any help would be great and thank you in advance!

Answer 1

I think that You should take a look here - Excel VBA reference for Inet objects it is shown here how to add refernce for INet objects in vba. Furthermore when You just want to test if the code works, instead of assigning macro to button and so on, if You use "Function" then when You go to worksheet cell and start to type =fnDown ... You should see Your macro - there You can put Your function parameters. However first of all You have to take care of the reference to Inet.

This link might also be helpful: VBA Excel and FTP with MSINET.OCX and Inet type