Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: open file, with winzip first

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need a macro that will open a winzip file, in order to extract the specfic file located within. I have tried using the macro recorder in trying this, of course with no luck, and I can't find anything in my excel books.

    Can anyone help me?????????



    _________________
    Russell

    [ This Message was edited by: ermccarthy on 2002-03-01 15:34 ]

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-01 15:33, ermccarthy wrote:
    I need a macro that will open a winzip file, in order to extract the specfic file located within. I have tried using the macro recorder in trying this, of course with no luck, and I can't find anything in my excel books.

    Can anyone help me?????????



    _________________
    Russell

    [ This Message was edited by: ermccarthy on 2002-03-01 15:34 ]
    Hi Russell

    have a look here

    http://www.mrexcel.com/board/viewtop...ic=776&forum=2

    Ivan

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK so---I'm really dumb.....

    The path to the zip file is:
    H:FidelioFO_dataHistorySpool

    This is where I would select the zip file, in this case 20010817.zip

    And out of this zip file I need to open spool1.xls. No Changes will be made to the file, I am just going to copy the data to my current worksheet and close it, so I don't think I need to extract it.....

    I can get this far:

    SubZipFile()
    Dim ZipPath as String
    Dim ZipIt As String
    Dim Source As String
    Dim Dest As String

    ZipPath="H:FidelioFO_dataHistorySpool"


    From that point I get lost, I can handle the copy and paste routines, I am just lost getting this file to open.

    PLEASE help!!!!I think I am actually a little more confused then when I started.


    Russell

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try

    Function WinZipIt(Source As String, Target As String, zip As Boolean)
    Dim ZipIt As String
    Dim UnZipIt As String

    ZipIt = "C:Program filesWinzip" & "winzip32 -a "
    UnZipIt = "C:Program FilesWinzip" & "winzip32 -e "

    If zip Then
    Shell (ZipIt & Target & Source)
    Else
    Shell (UnZipIt & Target & Source)
    End If

    End Function

    Sub TestZip()
    WinZipIt " C:UnzippedFilesHere", " H:\Fidelio\FO_data\History\Spool\20010817.zip", False
    End Sub

    Ivan


  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    NB: Just noticed + read the MVP's forum

    The / that show in my post should ONLY be
    ONE there are extra "/" added.

    Ivan

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ivan, I had to make a small change to the macro you forwarded to me, as the winzip files are renamed with a suffix of spl instead of zip. It works great, but here is my delema, I need to specify which file to open, so I have made a cell on the worksheet (named ppfile1) that is where I will type in the file name I need to open (it is actually the date YYYYMMDD.spl) I can not seem to get it to work using the normal naming conventions I normally use in my programming,

    any ideas on how to correct this??

    Function WinZipIt(Source As String, Target As String, spl As Boolean)
    Dim ZipIt As String
    Dim UnZipIt As String

    ZipIt = "C:Program filesWinzip" & "winzip32 -a "
    UnZipIt = "C:Program FilesWinzip" & "winzip32 -e "

    If spl Then
    Shell (ZipIt & Target & Source)
    Else
    Shell (UnZipIt & Target & Source)
    End If

    End Function

    Sub priorperiod()
    filepp = Range("ppfile1").Value
    WinZipIt " C:UnzippedFilesHere", filepp, False
    ''This is where I get a syntax error (filepp)''

    End Sub

    _________________
    Russell

    [ This Message was edited by: ermccarthy on 2002-03-03 13:46 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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