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