Search code examples
vbaexcelsftpwinscpwinscp-net

Getting "user defined type not defined" when trying to use WinSCP .NET assembly in VBA to upload file to SFTP


I am trying to develop code for save the file in SFTP server but error in Session and showing error as

user defined type not defined

I am not sure below code working or not and I copied the code from internet source.

Any suggestion would be appreciated.

Sub test()
Dim wb As Workbook
Set wb = "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, wb)

    ' 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 Workbook) 'error line
 Dim wb As Workbook
 Set wb = Workbooks(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

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

    Dim transferResult As TransferOperationResult
    Set transferResult = mySession.PutFiles(wb1, "/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

Solution

  • Make sure you have followed the instructions for: