VBA/Shell script not working for large files

Advertisement

ragordy
Joined:
Posts:
4
Location:
Atlanta, GA

VBA/Shell script not working for large files

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:

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:

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

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
28,094
Location:
Prague, Czechia

Re: VBA/Shell script not working for large files

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.

Reply with quote

ragordy
Joined:
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?

> 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

Reply with quote

ragordy
Joined:
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:

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.

Reply with quote

ragordy
Joined:
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.

Reply with quote

Advertisement

martin
Site Admin
martin avatar
Joined:
Posts:
28,094
Location:
Prague, Czechia

ragordy wrote:

Strangely, double-quoting the string in VBA had no effect in WinSCP
Please read documentation.
_________________
Martin Prikryl

Reply with quote

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.

Reply with quote

Advertisement

You can post new topics in this forum