Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Opening Zip? (Excel xp)

    I daily update a file that comes to me in Zipped format. Currently, I just open it to a window, copy and paste the contents into a certain tab in my ss, then do a Text-to-columns parse. I wonder if one can set a macro up to do such a task--basically: unzip and import a zipped file into a spreadsheet and parsing it coming-in (; separator.) Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro Opening Zip? (Excel xp)

    If you have WinZip, you can download and install the command line interface for WinZip. This enables you to zip and unzip files using MS-DOS style commands. You could call this from an Excel macro.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Opening Zip? (Excel xp)

    Thanks. Give me a reference so I can look up how to do this in the QUE book I have. What would be the topic (as they would think it)? "Running other programs from macros" or something?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro Opening Zip? (Excel xp)

    Look for the Shell instruction.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro Opening Zip? (Excel xp)

    You might also want to look at SendKeys in addition to Shell

    Shell to open winzip and sendkeys to send the statements.

    Steve

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

    Re: Macro Opening Zip? (Excel xp)

    Torquemada,

    In addition to Hans' and Steve's comments, the code below is what I use. It needs the command line addin (see Hans' link above) installed on your machine, which in turn requires a registered copy of winzip, v8.1 or higher. Note that although I've made the output path a required parameter, only the archive name is actually required. RetVal will return zero if all is successful and non-zero if not. See also <!post=this post,308965>this post<!/post> for info on zipping files.



    Option Explicit

    'global declaration
    Public Const PATH_WINZIPUNZIP As String = "c:Program Fileswinzipwzunzip.exe"

    Sub TestCallUnZip()
    Call UnZipFiles("c:zippyGLS-agedbacklog","c:zippybungle")
    End Sub

    Sub UnZipFiles(strArchiveName As String, strOutputPath As String, _
    Optional strUnZipOptions As String, Optional strFileList As String)
    'unzip daily input files
    '~~~~~~~~~~~~~~~~~~~~~
    'declare variables
    Dim retval As Double
    '~~~~~~~~~~~~~~~~~~~~~
    'add spacing for command line call
    If Len(strUnZipOptions) > 0 Then strUnZipOptions = " " & strUnZipOptions
    strArchiveName = " " & strArchiveName
    strOutputPath = " " & strOutputPath
    If Len(strFileList) > 0 Then strFileList = " " & strFileList
    '~~~~~~~~~~~~~~~~~~~~~
    'unzip the files
    retval = ExecCmd(PATH_WINZIPUNZIP & strUnZipOptions & _
    strArchiveName & strOutputPath & strFileList)
    Debug.Print retval
    End Sub

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Opening Zip? (Excel xp)

    Thanks for the info. Let me work on that a bit...find out more about registering and if we already are.

Posting Permissions

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