Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    WinZip and XL VBA (97; SR2)

    I recently lost my archive of VBA code and it included code to zip a laundry list of XL files into one Zip file.

    Someone responded to a post I made a year or two ago and provided a solution. Unfortunately the search feature is unavailable ie my request for assistance.

    Thanks,
    John

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WinZip and XL VBA (97; SR2)

    After surfing the web and playing around I came up with the code in the attached file.

    Please don't ask me what happend to my archive/backup. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    John

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: WinZip and XL VBA (97; SR2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> John

    OK check the WinZip web site for the Command Line switches, and an example that works in VB and VBA.

    I have done some work in this area, but never got it really to work well.

    Partly because you have to convert the Long File Name into the short version via API, and also if you have a multi-file Zipped Archive, there is no way I know about to extract only one file.

    I don't know how long it will take to dig up that code, but try the web site first, and see where you go from there, and send me a note and I'll help you along the way.

    Sorry <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WinZip and XL VBA (97; SR2)

    John,

    The following code works for me: However:
    You do need at least version 8.1 of winzip and the command line extension as well.
    The copy of winzip you have needs to be registered or it just won't work.
    The help files are worth reading and do actually help.
    The code is written for 2K but shouldn't need changing.

    HTH


    'global variables
    Public Const PATH_ARCHIVE As String = "D:archivecurrent"
    Public Const PATH_WINZIPZIP As String = "C:Program Fileswinzipwzzip.exe"
    Public Const PATH_WINZIPUNZIP As String = "C:Program Fileswinzipwzunzip.exe"
    Public Const PATH_REPS_EMAIL As String = "C:reportingdailiesreports-email"

    <pre>Sub ZipFiles(strArchiveName As String, _
    strInputPath As String, _
    strInputMask As String, _
    Optional intDaysAgo As Integer = 0)
    'zip daily input files
    '~~~~~~~~~~~~~~~~~~~~~
    'declare variables
    Dim retval As Double
    Dim strZipOptions As String
    Dim strZipInput As String
    Dim strZipOutput As String
    '~~~~~~~~~~~~~~~~~~~~~
    'set variables
    strZipOptions = " -aexrp " 'see helpfile for options
    strZipOutput = PATH_ARCHIVE & strArchiveName & "-" _
    & Format(Date - intDaysAgo, "yyyymmdd") & ".zip "
    strZipInput = strInputPath & strInputMask
    '~~~~~~~~~~~~~~~~~~~~~
    'zip the files
    retval = ExecCmd(PATH_WINZIPZIP & strZipOptions & strZipOutput & strZipInput)
    Debug.Print retval
    End Sub
    </pre>


    'sample call
    Call ZipFiles("dailyoutputfiles", PATH_REPS_EMAIL, "*.*", 1)

Posting Permissions

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