Jump to content
Visual Boy Advance-M

VBA Script to automate WinSCP File Upload

Recommended Posts

Just wanted to share the code which would automatically upload files to remote FTP server using WinSCP

Step-1:Copy the below XL VBA code to a new Module in VBA.


Option Explicit

Private Declare Function CloseHandle Lib "kernel32" ( _

ByVal hObject As Long) As Long

Private Declare Function GetExitCodeProcess Lib "kernel32" ( _

ByVal hProcess As Long, lpExitCode As Long) As Long

Private Declare Function OpenProcess Lib "kernel32" ( _

ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _

ByVal dwProcessId As Long) As Long

Public Function ShellX( _

ByVal PathName As String, _

Optional ByVal WindowStyle As VbAppWinStyle = vbMinimizedFocus, _

Optional ByVal Events As Boolean = True _

) As Long



Const STILL_ACTIVE = &H103&


Dim ProcId As Long

Dim ProcHnd As Long



'Get process-handle:

ProcId = Shell(PathName, WindowStyle)

ProcHnd = OpenProcess(PROCESS_QUERY_INFORMATION, True, ProcId)


'wait for process end:


If Events Then DoEvents

GetExitCodeProcess ProcHnd, ShellX

'Debug.Print ShellX

If ShellX <> STILL_ACTIVE Then

Exit Do

End If

Loop While ShellX = STILL_ACTIVE


'clean up:

CloseHandle ProcHnd


End Function




Sub testdunno()

Dim x As Long

Dim string1 As String

'string1 = "C:\Users\user\Desktop\code1.txt.bat"

'cscript /nologo C:\Users\user\Desktop\codet.bat> C:\text.txt

string1 = "cscript" & " /nologo" & " example.js"

'Debug.Print string1

x = ShellX(string1)

End Sub


Step-2: Now create a JS File with name "example.js" in the same folder where you have the above VBA Macro code[ (Enter the Winscp path in your desktop, the Usercredentials to connect to FTP and the FTP server name etc in the below code in marked places)/b]



// Configuration

// Remote file search for

// Session to connect to

var SESSION = "session";

// Path to winscp.com

var WINSCP = "C:\\Program Files (x86)\\WinSCP\\WinSCP.com"; //Put your WinSCP file path here

var filesys = WScript.CreateObject("Scripting.FileSystemObject");

var shell = WScript.CreateObject("WScript.Shell");

var exec;

var ForReading = 1, ForWriting = 2, ForAppending = 3;


var TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0;


var fs, f, ts, s;


fs = new ActiveXObject("Scripting.FileSystemObject");


fs.CreateTextFile( "C:\\test.txt" ); // Create a file

// run winscp to check for file existence


fs = new ActiveXObject("Scripting.FileSystemObject");

fs.CreateTextFile( "C:\\test1.txt","True"); // Create a file

exec = shell.Exec("\"" + WINSCP + "\"");


"option batch abort\n" +

"open "+ "ftp://Username:Password@ServerName" +"\n" +

//"cd " + "C:\\Users\\user\\" + "Documents" + "\n"+

//"cd " +"My " + "Documents"+ "\n"+

"put " + "File to Upload" + " /" + "\n" +

"EXIT" + "\n");

while (exec.Status == 0)





//WScript.Echo("Error checking for file existence")> "C:\\text2.txt";

//WScript.Echo("Error checking for file existence");



f = fs.GetFile("C:\\test.txt");

ts = f.OpenAsTextStream(ForWriting, TristateUseDefault);


ts.Write( exec.StdOut.ReadAll() );


ts.Close( );



if (exec.ExitCode != 0)



//WScript.Echo("Error checking for file existence") > "C:\text.txt.txt";



// WScript.Echo("Error checking for file existence") >> "C:\text.txt.txt";




// wait until the script finishes

Share this post

Link to post
Share on other sites
This topic is now closed to further replies.