Topic "VBA Excel 2010 64bit hangs"

Author Message
cts1085
[View user's profile]

Joined: 2016-02-27
Posts: 4
First off - thank you for providing this interface - i hope to get it working from within a VBA script soon.

I downloaded the library and to test i was able to execute this script from powershell: (Note I did change some of the strings to protect passwords, etc.)

Code:
try
{
    # Load WinSCP .NET assembly
    Add-Type -Path "c:\Program Files (x86)\WinSCP\WinSCPnet.dll"
 
    # Setup session options
    $sessionOptions = New-Object WinSCP.SessionOptions -Property @{
        Protocol = [WinSCP.Protocol]::Sftp
        HostName = "hostname"
        UserName = "*****"
        Password = "*****"
        SshHostKeyFingerprint = "ssh-rsa 2048 ce:f2:d5:05:e0:36:63:e1:**:**:**:c0:16:ba:06:72"
    }
   
   
 
    $session = New-Object WinSCP.Session
 
    try
    {
        # Connect
        $session.Open($sessionOptions)
 
        # Upload files
        $transferOptions = New-Object WinSCP.TransferOptions
        $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary
 
        $transferResult = $session.PutFiles("d:\util\junkfolder\*", "/var/lib/user/junkfolder/", $False, $transferOptions)
 
        # Throw on any error
        $transferResult.Check()
 
        # Print results
        foreach ($transfer in $transferResult.Transfers)
        {
            Write-Host ("Upload of {0} succeeded" -f $transfer.FileName)
        }
    }
    finally
    {
        # Disconnect, clean up
        $session.Dispose()
    }
 
    exit 0
}
catch [Exception]
{
    Write-Host $_.Exception.Message
    exit 1
}


I hope this shows that the DLL is functioning..

Now when I try to execute this VBA script from within Excel 2010 64bit Macro (And I do have the library reference selected) via debug - the mySession and mySessionOptions variables are never set - it is like the "New" option is not finding the objects.

I also ran the .net registration for both 32bit and 64bit per your install_library page.

Thoughts?

Code:
Sub TransferFolder()
 
    On Error GoTo SPC_err
         
    ' Setup session options
    Dim mySessionOptions As New WinSCPnet.SessionOptions
   
    With mySessionOptions
        .Protocol = WinSCPnet.Protocol_Sftp
        .HostName = "hostname"
        .UserName = "******"
        .Password = "******"
        .SshHostKeyFingerprint = "ssh-rsa 2048 ce:f2:d5:05:e0:36:63:e1:**:**:**:c0:16:ba:06:72"
    End With
   
    Dim mySession As New WinSCPnet.Session
   
    ' Connect
    mySession.Open mySessionOptions
   
    On Error Resume Next
   
    ' Upload files
    Dim myTransferOptions As New WinSCPnet.TransferOptions
    myTransferOptions.TransferMode = WinSCPnet.TransferMode_Binary
     
    Dim transferResult As WinSCPnet.TransferOperationResult
   
    Set transferResult = mySession.PutFiles("d:\util\junkfolder\*", "/var/lib/amavis/junkfolder/", False, myTransferOptions)
     
    ' Throw on any error
    transferResult.Check
     
    ' Display results
    Dim transfer As WinSCPnet.TransferEventArgs
    For Each transfer In transferResult.Transfers
        MsgBox "Upload of " & transfer.Filename & " succeeded"
    Next
   
    ' Query for errors
    If Err.Number <> 0 Then
        MsgBox "Error: " & Err.Description
 
        ' Clear the error
        Err.Clear
    End If
     
    ' Disconnect, clean up
    mySession.Dispose
    mySessionOptions.Dispose
     
    ' Restore default error handling
    On Error GoTo 0
    Exit Sub
SPC_err:
    MsgBox "Error: " & Err.Description
   
End Sub
 
Advertisements
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24991
Location: Prague, Czechia
Quote:
the mySession and mySessionOptions variables are never set

What does that mean? Does the code even compile/run? Or does it fail on run time? With what error?
cts1085
[View user's profile]

Joined: 2016-02-27
Posts: 4
prikryl wrote:
Quote:
the mySession and mySessionOptions variables are never set

What does that mean? Does the code even compile/run? Or does it fail on run time? With what error?


ok - thanks - that helps.

Then - why does the code hang on the next statement? Outlook stops responding, etc.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24991
Location: Prague, Czechia
cts1085 wrote:
why does the code hang on the next statement?

What statement?
cts1085
[View user's profile]

Joined: 2016-02-27
Posts: 4
prikryl wrote:
cts1085 wrote:
why does the code hang on the next statement?

What statement?


This statement:

Code:
With mySessionOptions
        .Protocol = WinSCPnet.Protocol_Sftp
        .HostName = "hostname"
        .UserName = "******"
        .Password = "******"
        .SshHostKeyFingerprint = "ssh-rsa 2048 ce:f2:d5:05:e0:36:63:e1:**:**:**:c0:16:ba:06:72"
    End With


When i step through the debugger and it hits this statement - it hangs.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24991
Location: Prague, Czechia
Did you try to separate the With statement to separate statements?

Also I'm not sure about your .Protocol = WinSCPnet.Protocol_Sftp syntax. Did you try .Protocol = Protocol_Sftp, as documented?
https://winscp.net/eng/docs/library_vb#enums
cts1085
[View user's profile]

Joined: 2016-02-27
Posts: 4
I changed the code to what you recommended.
I added a msgbox "test" in front of the "with" and when stepping I see the msgbox but the next command hangs or exits Outlook.

I looked in the event viewer and found this: not sure if it is helpful.


Code:
- <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
- <System>
  <Provider Name=".NET Runtime" />
  <EventID Qualifiers="0">1023</EventID>
  <Level>2</Level>
  <Task>0</Task>
  <Keywords>0x80000000000000</Keywords>
  <TimeCreated SystemTime="2016-03-03T16:33:33.000000000Z" />
  <EventRecordID>85015</EventRecordID>
  <Channel>Application</Channel>
  <Computer>CTS</Computer>
  <Security />
  </System>
- <EventData>
  <Data>.NET Runtime version 2.0.50727.5485 - CLR: Fatal Execution Engine Error (000007FEED75600A) (80131506)</Data>
  </EventData>
  </Event>
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24991
Location: Prague, Czechia
I'm sorry, but I do not know how to help you further.
Advertisements

You can post new topics in this forum






Search Site

What is WinSCP?

It is award-winning SFTP client, SCP client, FTPS client and FTP client integrated into one software program for file transfer to FTP server or secure SFTP server. [More]

And it's free!

Donate

About donations

$9   $19   $49   $99

About donations

Recommend

WinSCP Privacy Policy

WinSCP License