Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Unanswered: Shell FTP question - I give up: HELP!

    I did a couple of searches but I keep getting non-Access results so forgive me if I missed this topic.

    I have created 2 scripts to FTP (using VBA to shell out) into a remote server and grab all the files there in a given folder. The first actually just calls the 2nd. These work very nicely in a command line environment - double click, and voila, files.

    When I use a simple Shell command inside Access, 'it don't work'. I have scoured the web and tried lots of code - unfortunately I don't understand all of the examples I've tried and feel this should boil down to a couple of lines, not multiple modules and procedures (I could obviously be mistaken). I know in a perfect world I would use a variation of "shell and wait" coding to get the results but right now I would settle for this fracking little script working!

    My batch files are as follows:

    FILEone: "GETscriptTEST.bat"
    open targetsite.com
    user
    password
    lcd TEST
    cd //ftp/UPLOADS
    mget *.*
    bye

    END of CODE

    FILEtwo: "DownloadTEST.bat"
    ftp -s:GETscriptTEST.bat

    End

    In my triggered event I use this line of code:
    RetVal = Shell("H:\processing\Production\DownloadTEST.bat")

    I've also tried:
    Call Shell("H:\processing\Production\DownloadTEST.bat", vbMaximizedFocus)
    and
    Shell ("H:\processing\Production\DownloadTEST.bat")

    I can shell out to move processed files to new folders using Dos shell commands but the FTP script fails every time.

    I am a gonzo programmer and I learned half of what I know from reading this forum and other bits from the web. Forgive my ignorance, but can anyone point me in the right direction?

    Much thanks in advance!
    Flipper
    "Don't you hate Perry's wife?"

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You should probably supply the full path of GETscriptTEST.bat in DownloadTEST.bat.

    Rummaging in my archive box, I've found this piece of code that could help in your quest for a variation of "shell and wait":
    Code:
    Option Compare Database
    Option Explicit
    Rem
    Rem ===============
    Rem  LAUNCH MODULE
    Rem ===============
    Rem
    Rem Created amg 2000-02-13
    Rem
    Rem Launch external app and wait for user exit before continuing Main App.
    Rem
    Rem Works with Win NT 4.0 and up.
    Rem
    Rem Procedure to follow if used to manage external text file (e.g. with worpad.exe).
    Rem
    Rem Case One : new file
    Rem --------
    Rem            Create an empty file (i.e. in Temp directory).
    Rem            call sub with wordpad and the text file name.
    Rem            when back in app, copy it in storage directory.
    Rem
    Rem    Dim Channel As Integer
    Rem    Channel = FreeFile
    Rem    Open "c:\lulu.txt" For Output As Channel
    Rem    Close Channel
    Rem    LauchAndWaitForUserExit "c:\program files\windows nt\accessoires\wordpad.exe", "c:\lulu.txt"
    Rem
    Rem
    Rem Case Two : the file exists
    Rem --------
    Rem            Copy the file from storage directory (i.e. in Temp directory).
    Rem            call sub with wordpad and the text file name.
    Rem            when back in app, copy it in storage directory.
    Rem
    Rem Note : Could be enhanced by checking if file was modified.
    Rem ----
    
    Private Type STARTUPINFO
        cb As Long
        lpReserved As String
        lpDesktop As String
        lpTitle As String
        dwX As Long
        dwY As Long
        dwXSize As Long
        dwYSize As Long
        dwXCountChars As Long
        dwYCountChars As Long
        dwFillAttribute As Long
        dwFlags As Long
        wShowWindow As Integer
        cbReserved2 As Integer
        lpReserved2 As Long
        hStdInput As Long
        hStdOutput As Long
        hStdError As Long
    End Type
    
    Private Type PROCESS_INFORMATION
        hProcess As Long
        hThread As Long
        dwProcessID As Long
        dwThreadID As Long
    End Type
    
    Private Const NORMAL_PRIORITY_CLASS = &H20&
    Private Const INFINITE = -1&
    
    Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
             hHandle As Long, ByVal dwMilliseconds As Long) As Long
    Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
             lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
             lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
             ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
             ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
             lpStartupInfo As STARTUPINFO, lpProcessInformation As _
             PROCESS_INFORMATION) As Long
    Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
    
    Sub LauchAndWaitForUserExit(AppSpec As String, Optional FileSpec As String)
        
        If IsMissing(FileSpec) Then
            ExecCmd (AppSpec)
        Else
            ExecCmd (AppSpec & "  " & FileSpec)
        End If
    
    End Sub
    
    Public Sub ExecCmd(cmdline$)
    
        Dim NameOfProc As PROCESS_INFORMATION
        Dim start As STARTUPINFO
        Dim ReturnValue As Integer
        
        ' Initialize the STARTUPINFO structure
        start.cb = Len(start)
        
        ' Start the shelled application
        ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, NameOfProc)
        ReturnValue = WaitForSingleObject(NameOfProc.hProcess, INFINITE)
        ReturnValue = CloseHandle(NameOfProc.hProcess)
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Oct 2003
    Posts
    18
    Thanks for the advice, maybe it's just a reference problem.

    This code is probably just what I need but sadly I don't completely understand what it's doing so I don't think I'm going to be able to implement it. Would all of this go into a module and then in my form I would call my BAT script using ExecCMD("c:\MYbatFILE.bat) and it would launch and complete its execution while Access waits? Sorry this is above my head.

    I guess I still have much to learn. I'll study it more when time permits, one of the limitations of being self taught. All the same thanks for the help!

    Very much appreciated!

    Flipper
    "Don't you hate Perry's wife?"

  4. #4
    Join Date
    Oct 2003
    Posts
    18
    Success!

    Thanks so much, I didn't realize the reference was the entire problem.

    As a bonus I also got ShellWait to work although I had to use different code that used methods I understand a little better.

    Props to Sinndho!!

    Thanks, Flipper
    "Don't you hate Perry's wife?"

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •