Topic "VBA/Shell script not working for large files"

Author Message
ragordy
[View user's profile]

Joined: 2009-04-09
Posts: 4
Location: Atlanta, GA
I have written the following function in MS Access/VBA (thanks to this site) and it works fine with every file I send except for a file nearly 1GB in size:

Code:
Public Sub WinSCP_Send(strFile As String, strLogin As String, strLocalDirectory As String, strRemoteDirectory As String)
'send file using WinSCP, deletes remote instance (if any) file before saving

Dim strWinSCPPath As String
Dim strShell As String, strDate As String

    strWinSCPPath = "C:\Program Files\WinSCP\WinSCP.exe"
   
    If Dir(strWinSCPPath) = "" Then
        strWinSCPPath = "C:\Program Files\WinSCP3\WinSCP3.exe"
            If Dir(strWinSCPPath) = "" Then
                MsgBox "Please verify that WinSCP is at C:\Program Files\WinSCP\WinSCP.exe (or, C:\Program Files\WinSCP3\WinSCP3.exe) and try again.", vbCritical, "Error: WinSCP.exe Not Available"
                Exit Sub
            End If
    End If
   
    strShell = Chr(34) & strWinSCPPath & Chr(34) & " " & strLogin & " /command ""rm " & strRemoteDirectory & strFile & """ ""exit"""
    Shell strShell
   
    strShell = Chr(34) & strWinSCPPath & Chr(34) & " " & strLogin & " /command ""put " & strLocalDirectory & strFile & " " & strRemoteDirectory & """ ""exit"""
    Shell strShell

End Sub


The shell script follows:

Code:
"C:\Program Files\WinSCP\WinSCP.exe" user:password@server /command "put C:\Test.csv /path/" "exit"


The file is being sent to a UNIX server from a Windows desktop. I am basing this script on the Scripts page item for sending a single file. I do not understand why it seems to work for every file but the large one. I would assume the process would hang for 60 seconds or so while the transfer executes, but it only hangs for a second.

Is something wrong with my script?

Do I need to add a pause somewhere?

Preferably, I want the process to be seamless to the user, but, if I cannot get this to work she will have to use the WinSCP GUI to send this large file. Any help will be greatly appreciated. Using WinSCP 4.1.8.

TIA,
Gordy.
Advertisements
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24530
Location: Prague, Czechia
Please post a full log file showing the problem.

To generate log file, enable logging, log in to your server and do the operation and only the operation that causes the error. For posting extensive logs you may use pastebin or similar application. Note that passwords and passphrases not stored in the log. You may want to remove other data you consider sensitive though, such as host names, IP addresses, account names or file names (unless they are relevant to the problem). If you do not want to post the log publicly, you may email it to me. You will find my address (if you log in) in my forum profile. Please include link back to this topic in your email. Also note in this topic that you have emailed the log.

Otherwise your script is not good one. It does not anable batch mode, when would make your script hang in case of any error. See the examples and general documentation of the scripting.
ragordy
[View user's profile]

Joined: 2009-04-09
Posts: 4
Location: Atlanta, GA
I enabled logging, but it doesn't appear to show anything. Should it be logging activities that occur outside of the GUI?

Code:
> 2009-04-10 06:48:57.051 Type: SSH_FXP_OPENDIR, Size: 20, Number: 1291
< 2009-04-10 06:48:57.051 Type: SSH_FXP_STATUS, Size: 24, Number: 1028
. 2009-04-10 06:48:57.051 Discarding reserved response
< 2009-04-10 06:48:57.102 Type: SSH_FXP_HANDLE, Size: 13, Number: 1291
> 2009-04-10 06:48:57.112 Type: SSH_FXP_READDIR, Size: 13, Number: 1548
< 2009-04-10 06:48:57.689 Type: SSH_FXP_NAME, Size: 9067, Number: 1548
> 2009-04-10 06:48:57.689 Type: SSH_FXP_READDIR, Size: 13, Number: 1804
< 2009-04-10 06:48:57.750 Type: SSH_FXP_STATUS, Size: 28, Number: 1804
< 2009-04-10 06:48:57.750 Status/error code: 1
> 2009-04-10 06:48:57.750 Type: SSH_FXP_CLOSE, Size: 13, Number: 2052
ragordy
[View user's profile]

Joined: 2009-04-09
Posts: 4
Location: Atlanta, GA
I got the log working, and the problem is with the file path...it has a space. I'll fix this and hopefully it'll solve my problem. I also edited my script as follows:

Code:
C:\Program Files\WinSCP\WinSCP.exe /console /command "option batch on" "open user:password@server" "put C:\File.txt /serverpath/" "exit"


Thanks for the tips.
ragordy
[View user's profile]

Joined: 2009-04-09
Posts: 4
Location: Atlanta, GA
Yep, removing the space seems to have fixed it. Strangely, double-quoting the string in VBA had no effect in WinSCP, so I have asked the client to rename the parent folder to remove the space. Thanks again.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24530
Location: Prague, Czechia
ragordy wrote:
Strangely, double-quoting the string in VBA had no effect in WinSCP

Please read documentation.
_________________
Martin Prikryl
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24530
Location: Prague, Czechia
See how to use WinSCP .NET assembly from VBA:
http://winscp.net/eng/docs/library_vb
shashi.singh

Guest


Hi Guys,

I have a SFTP server that I connect to using id and password through WINSCP. I am trying to write a VBA script that could allow me to connect and download the latest file posted on the location.

Please can you help me with this.
martin
[View user's profile]
Site Admin
Joined: 2002-12-10
Posts: 24530
Location: Prague, Czechia
shashi.singh wrote:
I have a SFTP server that I connect to using id and password through WINSCP. I am trying to write a VBA script that could allow me to connect and download the latest file posted on the location.

See https://winscp.net/eng/docs/script_download_most_recent_file
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