Jump to content
Visual Boy Advance-M
unnimech

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

 

'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: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.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

Share this post


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

×