VBA Excel 2010 64bit hangs

Advertisement

cts1085
Joined:
Posts:
4

VBA Excel 2010 64bit hangs

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.)

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?

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
 

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
41,518
Location:
Prague, Czechia

Re: VBA Excel 2010 64bit hangs

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?

Reply with quote

cts1085
Joined:
Posts:
4

Re: VBA Excel 2010 64bit hangs

martin wrote:

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.

Reply with quote

cts1085
Joined:
Posts:
4

Re: VBA Excel 2010 64bit hangs

martin wrote:

cts1085 wrote:

why does the code hang on the next statement?
What statement?

This statement:

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.

Reply with quote

Advertisement

cts1085
Joined:
Posts:
4

Re: VBA Excel 2010 64bit hangs

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.


- <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>

Reply with quote

Advertisement

You can post new topics in this forum