Search code examples
vbawinscpwinscp-net

Error on Session.Open in Winscp - VBA


Hi I am trying to upload the file in WinSCP via VBA. the mySession.Open mySessionOptions got Error - Network error connection to "103.231.8.66" time out. i googled but didn't get idea. any suggestion would appreciated

 Sub test()
    Dim wbs As String
     wbs = "D:\Ashok\Work\Loan_Input_Template V8-Library.xlsx"
    Dim mySession As New Session

        ' Enable custom error handling
        On Error Resume Next

       Call Upload(mySession, wbs)

        ' Query for errors
        If Err.Number <> 0 Then
            MsgBox "Error: " & Err.Description

            ' Clear the error
            Err.Clear
        End If

        ' Disconnect, clean up
        mySession.Dispose

        ' Restore default error handling
        On Error GoTo 0

              '  wb.Close SaveChanges:=True

    'FileSystemObject.DeleteFile sPathName

    End Sub

     Private Sub Upload(ByRef mySession As Session, ByRef wb1 As String)  'error line
     Dim wb As Workbook
    Set wb = Workbooks.Open(wb1)
        ' Setup session options
        Dim mySessionOptions As New SessionOptions
        With mySessionOptions
            .Protocol = Protocol_SFTP
            .HostName = "103.231.8.66"
            .UserName = "username"
            .Password = "password"
            .SshHostKeyFingerprint = "ssh-ed25519 256 df:94:44:56:1b:c2:75:8b:b4:58:3a:e2:ef:2e:0d:78"
        End With

        ' Connect

        mySession.Open mySessionOptions ' ERROR LINE

        ' Upload files
        Dim myTransferOptions As New TransferOptions
        myTransferOptions.TransferMode = TransferMode_Binary
        Dim transferResult As TransferOperationResult

'i am not sure this line will working or not

        Set transferResult = mySession.PutFiles(wb, "/home/sftpcf/", False, myTransferOptions)

        ' Throw on any error
        transferResult.Check

        ' Display results
        Dim transfer As TransferEventArgs
        For Each transfer In transferResult.Transfers
            MsgBox "Upload of " & transfer.FileName & " succeeded"
        Next

    End Sub

error msg:

enter image description here

Manual Logging:

enter image description here


Solution

  • Finally found answer my question: Adding Port No .PortNumber = XXXX in mySessionOptions and i removed Set wb = Workbooks.Open(wb1). its works perfectly