title: Connecting to FTP from Excel to automate file sharing (VBA Beginner)
body: <p>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:</p>
  
  <pre><code>open ftp://myUsername:myPassword@theHostname/
  
  # Your command 1
  # Your command 2
  
  exit
  </code></pre>
  
  <p>I'm assuming the open line would connect Excel to FTP. I'm referencing code from this site to put into the '# command' area: <a href="https://www.mrexcel.com/forum/excel-questions/261043-connecting-ftp-excel.html" rel="nofollow noreferrer">https://www.mrexcel.com/forum/excel-questions/261043-connecting-ftp-excel.html</a></p>
  
  <pre><code>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:-" &amp; Err.Description
      Resume Xit
  End Function
  
  exit
  </code></pre>
  
  <p>I did as this site said to go to VBA Editor > Tools > reference and check off Microsoft Internet Control.</p>
  
  <p>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())</p>
  
  <p>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?</p>
  
  <p>I'm sorry I'm so confused :( Any help would be great and thank you in advance!</p>