Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Zip and Unzip Files???? (Access 97 or 2000)

    I am receiving text files that I import into my access database. The code for this is already done and working. What I want to do is be able to zip up a copy of the text files that I am using and then FTP them to another location before I run the rest of the code. If anyone knows of a way to do this, help would be greatly appreciated.
    Thanks

  2. #2
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zip and Unzip Files???? (Access 97 or 2000)

    How do you zip the files? If you use WinZip from Nico Mak software, they make a command-line interpreter that can be used in code to execute a zip command. You can also execute an FTP command from VBA code. We routinely create reports as RTF documents, zip them up and FTP them to remote sites.

    I've included the VBA code to accomplish this below, assuming WinZip is used. You may be able to modify it to use PKZip or something else.
    <font face="Georgia">

    Public Function FTPFile(FileName As String, Optional ByVal SourceFolder As String = "", Optional FTPAddress As String = "", Optional TargetFolder As String = "") As Boolean
    ' Create a batch file to use FTP to transfer the an import file to White Rose
    Dim varRet As Variant ' holds response from invoked commands, message boxes
    Dim intFile As Integer ' holds the sequential file number
    Dim intSuccess As Integer ' holds the number of successful transfers
    Dim strBatchFile As String ' holds the name of the batch file to invoke
    Dim strDrive As String ' holds the drive letter that the file reside on
    Dim strFTPCommands As String ' holds the ftp commands that are used by the batch file
    Dim strResults As String ' holds the results of the ftp command
    Dim strRecord As String ' holds the contents of a record from the results file

    ' First, if no SourceFolder is given, get if from the Locations table
    If Nz(SourceFolder) = "" Then SourceFolder = GetLocation("Apisys")

    ' Next, if there is no FTP address, get it from the Locations table
    If Nz(FTPAddress) = "" Then FTPAddress = GetLocation("FTP")

    ' Next, if there is no TargetFolder, get it from the Locations table
    If Nz(TargetFolder) = "" Then TargetFolder = GetLocation("Informix")

    ' Finally, add a trailing slash to the source folder if it is not there
    If Right(Trim(SourceFolder), 1) <> "" Then SourceFolder = Trim(SourceFolder) & ""

    ' Then, create a file with the FTP commands
    strFTPCommands = SourceFolder & "FTPFiles.cmd"
    intFile = FreeFile
    Open strFTPCommands For Output As intFile
    Print #intFile, "user informix wrose"
    Print #intFile, "cd " & LCase(TargetFolder)
    Print #intFile, "put " & FileName & ".txt"
    Print #intFile, "bye"
    Close intFile

    ' Next, create a batch file to invoke the FTP command and capture the output
    strBatchFile = SourceFolder & "FTPFiles.bat"
    strResults = SourceFolder & "FTPFiles.out"
    strDrive = GetDrive(strBatchFile)
    intFile = FreeFile
    Open strBatchFile For Output As intFile
    Print #intFile, strDrive
    Print #intFile, "cd " & Chr(34) & SourceFolder & Chr(34)
    Print #intFile, "ftp -n -s:FTPfiles.cmd " & FTPAddress & " > " & Chr(34) & strResults & Chr(34)
    Close intFile

    ' Then, kill the output file if it exists and
    ' invoke FTPFiles.bat to transfer the files and save the output to a file
    If Dir(strResults) <> "" Then Kill strResults
    DoCmd.OpenForm "frmWait", OpenArgs:="Transferring files to White Rose..."
    DoEvents
    varRet = Shell(strBatchFile, vbHide)

    ' Wait until the transfer is done, then get rid of the wait message
    While Dir(strResults) = ""
    Pause 5
    Wend
    DoCmd.Close acForm, "frmWait"

    ' Read the output to see if the transfers were successful
    intFile = FreeFile
    Open strResults For Input As #intFile
    While Not EOF(intFile)
    Line Input #intFile, strRecord
    If Left(strRecord, 3) = "226" Then intSuccess = intSuccess + 1
    Wend
    Close intFile

    ' If the file was transferred successfully, exit the function;
    ' otherwise, send an error message and ask the user if he wants to view the results
    If intSuccess = 1 Then
    MsgBox "Files transferred successfully"
    FTPFile = True
    Else
    FTPFile = False
    varRet = MsgBox("The file was NOT successfully transferred to White Rose.@The transfer of " & FileName & "failed.@Do you want to view the transfer results?", vbYesNo)
    If varRet = vbYes Then
    Shell "Notepad " & SourceFolder & "FTPFiles.out", vbNormalFocus
    End If
    End If

    End Function
    </font face=georgia>

Posting Permissions

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