Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2006

    Unanswered: Access Macros and FTP

    Trying to send a text file from the pc to ftp site using Access macros. It will call up the ftp window, but then the window will disappear very quickly, and I cannot even catch what it is doing. FTP commands are not even running, as the file does nto go out. I have also been reading you can use VB to run this, but I cannot get that to work either. Batch/FTP will work fine if run manually. Can someone plase be of assistance, as this is driving me crazy!

    batch file
    ftp.exe -s:filetorun.txt

    text file
    put c:\directory\filename.txt filedroppedas.txt

    Run App c:\directory\batchfiletorun.bat


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    I would forget about macros for such jobs and use VBA. It would give you all the control you need to manipulate external programs.
    Have a nice day!

  3. #3
    Join Date
    Apr 2006

    Access Macros and FTP

    There's my (very) weak point. What would I use to run the batch file from VB?

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 14
    Here's a way to launch any kind of application. The function ShellExec receives the name of a file (it does not need to be a program). If the file extension is registered into the system (i.e. if you can open it by double-clicking on its icon in Windows Explorer), then the corresponding program will start and "open" the file. By passing the appropriate constants to the function ShellExec or ExecCmd you can decide of the size and position of the window for the application you launch. Calling the WaitForSingleObject function allows you to freeze the execution of your program until the external application terminates.
    Option Compare Database
    Option Explicit
    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
        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
    Public Const SW_HIDE As Long = 0          ' Hides the window and activates another window.
    Public Const SW_SHOWNORMAL As Long = 1    ' Activates and displays a window. Use this flag when displaying the window for the first time.
    Public Const SW_MAXIMIZE As Long = 3      ' Maximizes the specified window.
    Public Const SW_SHOW As Long = 5          ' Activates the window and displays it in its current size and position.
    Public Const SW_MINIMIZE As Long = 6      ' Minimizes the specified window and activates the next top-level window in the z-order.
    Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
        ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
        ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    Function ShellExec(ByVal FileName As String)
        ShellExecute &H0, vbNullString, FileName, vbNullString, vbNullString, SW_SHOWNORMAL
    End Function
    Public Sub ExecCmd()
        Dim start As STARTUPINFO
        Dim ReturnValue As Integer
        ' Initialize the STARTUPINFO structure
        start.cb = Len(start)
        ' Start the shelled application
        ReturnValue = CreateProcessA(&H0, "AcroRd32.exe " & Chr(34) & "C:\Documents and Settings\Sinndho\My documents\Windows Azure No SQL White Paper.pdf" & Chr(34), _
                                        0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, NameOfProc)
        ReturnValue = WaitForSingleObject(NameOfProc.hProcess, INFINITE)
        ReturnValue = CloseHandle(NameOfProc.hProcess)
    End Sub
    Function test()
        ShellExec "C:\Documents and Settings\Sinndho\My documents\Windows Azure No SQL White Paper.pdf"
    End Function
    Have a nice day!

Posting Permissions

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