Jump to content
Visual Boy Advance-M

unnimech

Members
  • Content Count

    0
  • Joined

  • Last visited

Community Reputation

0 Neutral

About unnimech

  • Rank
    Newbie
  • Birthday 06/14/1985
  1. 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
×