VBA Script to automate WinSCP File Upload

Non VBA-M Related on-topic discussion goes here, anything posted related to the emulator in this board will either be deleted or the content moved at staff discretion.
Locked
unnimech
Junior Member
Posts: 1
Joined: Sun Feb 03, 2013 11:01 pm

VBA Script to automate WinSCP File Upload

Post by unnimech »

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

 


'Declarations:


Const STILL_ACTIVE = &H103&


Const PROCESS_QUERY_INFORMATION = &H400&


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:


Do


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



//WScript.Echo(WScript.arguments(0));



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



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



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



exec.StdIn.Write(



"option batch abort\n" +



"open "+ "ftp://Username :P assword@ServerName" +"\n" +



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



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



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



"EXIT" + "\n");



while (exec.Status == 0)



{



WScript.Sleep(100);



//WScript.Echo(exec.ExitCode);



}



//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.Quit(1);



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



}



WScript.Echo(exec.StdOut.ReadAll());



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


 

 

 


// wait until the script finishes
Squall Leonhart
Posting Freak
Posts: 1217
Joined: Tue Mar 18, 2008 2:21 am

VBA Script to automate WinSCP File Upload

Post by Squall Leonhart »

This is not the forum you're looking for.
Locked