Looking for a VBscript for FTPing from access...

[Preka]

Anyone have a VBscript that can be used for transmitting via ftp in access? Alternatively, does anyone know a good site for looking for scripts such as this?

 

[drctx]

i've been using the dos ftp that comes with windows. i wrote code that creates the ftp batch file then runs it to ftp files from the mainframe to the server.
typing ftp at the dos prompt then ? will give a list of the ftp commands. it's not fancy but it works.

' rename ftpDownload.txt to ftpDownload.bat
newFTPBatchPath = Left(FTPBatchPath, Len(FTPBatchPath) - 3)
newFTPBatchPath = newFTPBatchPath & "bat"

' create text file ftpDownload.txt to directory
    Open FTPBatchPath For Output As #1
    Print #1, "Rem starts the dos ftp program and uses the script file ftpDownload.txt"
    Print #1, "Rem to automate the transfer of the files from the mainframe."
    Print #1, ""
    Print #1, "ftp.exe -s:" & Chr(34) & xPATH & "Data\ftp\ftpDownload.txt" & Chr(34)
    Close #1

Name FTPBatchPath As newFTPBatchPath

    ' create text file ftpDownload.txt to directory
        Open FTPFile For Output As #2
        Print #2, "open " & txtIPAddress
        Print #2, txtUsername
        Print #2, txtPassword
        Print #2, "cd .."
        Print #2, "ascii"
        Print #2, "get SFX.TEST.BN2RMC00.DATA2X " & Chr(34) & xPATH & "Data\MC00.txt" & Chr(34)
        Print #2, "get SFX.TEST.BN2RTCRM.DATA2X " & Chr(34) & xPATH & "Data\TCRM.txt" & Chr(34)
        Print #2, "get SFX.TEST.BN2RPOT1.DATA2X " & Chr(34) & xPATH & "Data\POT1.txt" & Chr(34)
        Print #2, "put " & Chr(34) & xPATH & "Data\Bhu5.txt" & Chr(34) & " SFX.TEST.BN2RHBU5.DATA2X"
        Print #2, ""
        Print #2, "bye"
        Close #2

' set path
ftpDownload = xPATH & "data\ftp\ftpDownload.bat"

Call ExecCmd(ftpDownload)

add this to a module. it waits for the app to finish running before it continues.

' variables for ExecCmd function
Public PAGE_HEADER_SUPCODE As String 'PRODUCER CODE FROM PAGE HEADER VIA EXTRACT PROCEEDURE
Public PLANTCODE As String 'PRODUCER CODE FROM PAGE HEADER VIA FILE CREATED BY EXTRACT PROCEEDURE
Public PLANTNAME As String 'PRODUCER NAME FROM PAGE HEADER VIA FILE CREATED BY EXTRACT PROCEEDURE
Public PLANTTYPE As String 'PRODUCT TYPE FROM PAGE HEADER VIA FILE CREATED BY EXTRACT PROCEEDURE
Public TBLX As String 'ALTERENATE PRODUCER CODE FROM USER (MUST LIVE IN TABLE A TO BE VALID)
Public X As Single 'array1 marker
Public StrTable_Name(20) As String 'ARRAY1 FROM PADOT.CFG FILE
Public TBL As String 'array1 variable for padot.cfg file table names
Public WEDT As String 'week ending date variable from input box in sub CONFIRM_WE_DATE
Public LASTX As Single 'the last table in array1
Public T As Single 'timer value (NOT USED)
Public WEDT_EMAIL As String 'WEEK ENDING DATE FROM 00 PAGE HEADER TABLE FOR E_MAIL FILE NAME
Public DELAY As Long ' NOT USED
Public FINDFILE As String 'find file for zip to confirm zip files have been written
Public TITLE As String
Public OLDTBLX As String
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

      Private Type PROCESS_INFORMATION
         hProcess As Long
         hThread As Long
         dwProcessID As Long
         dwThreadID As Long
      End Type

      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

      Private Const NORMAL_PRIORITY_CLASS = &H20&
      Private Const INFINITE = -1&

' Waits for the shelled application to finish before continuing to next line of code
Public Sub ExecCmd(cmdline$)
    Dim proc 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, proc)

    ' Wait for the shelled application to finish:
    Do
       ReturnValue = WaitForSingleObject(proc.hProcess, 0)
       DoEvents
       Loop Until ReturnValue <> 258

    ReturnValue = CloseHandle(proc.hProcess)
End Sub

 

[drctx]

here's a site with ftp information. look for FTP API about half way down the page.

http://vbnet.mvps.org/index.html?code/network/index.html

 

[BNPMike]

You could use cURL (the original one).

 

[Preka]

Thanks for the code and the link.

It looks like all of the information on that site is about downloading from an FTP site, though - I need to upload. Will the above code do that? I haven't had a chance to look it over yet (which will be time consuming since I don't really know squat about vbscript and will have to reason it out)

 

[drctx]

yes it will upload. just use the "put" command.

put c:\test.txt

or if you want to upload it as a different name

put c:\test.txt test_file.txt

 

[Preka]

Well, I have a process already that generates the file to be sent (it's a |-delimited file for use with a half-hearted edi system).

What I really need is a procedure I can stick on a button - preferably verbatim - so the people taking orders can occasionally click it and trasmit the already generated "edi.txt" to our shipper. I don't know a lick of vbscript really so the closer it is to a cut and paste job, the better. If anyone has anything like this, I would really appreciate it.

Thanks again.

 

[3plJAB]

Preka, I am also trying the half-hearted edi system in Access. I am using the OUTPUTTO macro command (with DELETEOBJECT after the transfer) with moderate success. (You will have to first design a report with all the delimited info from your query or table data.)

I also had a problem with this method creating a blank line at the beginning of the text file which is created. Go to Page setup on the File drop-down and set your top margin to zero or whatever defaults (this may depend on your default printer.... mine defaults to 0.166, but this works.)

(The reason I am surfing Tec-Tips, however, is retrieving EDI files that have a file extension of ".000". I am trying to find the VB command to rename these files with a ".txt" extension.)


AFCJAB

 

[psemianonymous]

---
What I really need is a procedure I can stick on a button - preferably verbatim
---

This is just not acceptable. You have to pull some weight, and make some attempt to understand what you're doing. All you have to do is modify what's been pasted above and go--you're simply asking too much.


As for renaming a file, you can use
[tt]filesystem.FileCopy("oldfilename", "newfilename")[/tt]
to copy the file to a new filename, or I believe the windows scripting host object has a rename function if copying the file is not acceptable.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[p27br]

How about the Internet Transfer Library :

http://www.mvps.org/access/modules/mdl0037.htm

 

[Craig0201]

AFCJAB,

You use the MoveFile method on the Scripting runtime object to rename a file.

Craig