Consulting

Results 1 to 16 of 16

Thread: Rename files at unzip!

  1. #1

    Rename files at unzip!

    Hi Everybody


    I need to unzip a series of .zip files - all located in a folder. Each of these zip files contain only one (1) file. A series of zip file can contain between 30 and 40 zip files.

    The idea is to be able to write a loop to unzip (using Winzip) the files from the zip files. This I have done - no problems here!

    The problem is that the zipped filename contains a serial number which is not a part of the name of the file contained in the zipped file. I need this serial number to be included as a part of the name of the this file that is contained in the zipped file.

    The code that I have used is shown below (not entirely my code)

    [vba]
    Sub UnZip_ZipFile(strZippedFileName As String, strTargetFolder As String, Optional strTargetFile As String)

    Const PATHWINZIP As String = "C:\progra~1\winzip\"
    Dim ShellStr As String
    ShellStr = PATHWINZIP & "Winzip32 -min -e -o" _
    & " " & Chr(34) & strZippedFileName & Chr(34) _
    & " " & Chr(34) & strTargetFolder & Chr(34)
    Shell ShellStr, vbHide

    'FileCopy strTargetFolder & Dir(strTargetFolder), strTargetFolder & strTargetFile ' & ".txt"

    End Sub
    [/vba]

    This seems to work ok for unzipping files. I tried to use the optional part with the FileCopy command but what that does is that it makes a copy of the zip file and changes the extension from .zip to ".txt" thereby rendereing it unreadable and useless. What I want is to rename the just unzipped file to the name stored in the variable "strTargetFile". This variable is something like this :"ThisFilename_1234.txt".

    Can anyone advise how can I catch the name of the unzipped file after the "Shell ShellStr, vbHide" command has been run?

    Or any othe way that I can rename the just unzipped file to another name?

    Some have used

    Namespace and things like that but I don't understand how it works.

    Please treat this with some degree of urgency.


    Best regards and thanks in advance.



    Deepak

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,954
    Location
    IF you want to rename a file, use Name.

    The problem with Shell is that is does not wait on the shelled program to complete. There are some ShellWait() threads that can help with that if needed.

    For your Name command, you will need to strip the .zip part out if you want to set a .txt filename. Obviously, to unzip, you would add the zip file extension back.

    The namespace methods might be more suitable than a shell method but that is your call. See Ron de Bruin's site for details. http://www.rondebruin.nl/tips.htm

    Here are some routines that you might find handy.
    [vba]Function GetFileName(filespec As String)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    GetFileName = fso.GetFileName(filespec)
    End Function
    Function GetFileExt(filespec As String)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    GetFileExt = fso.GetExtensionName(filespec)
    End Function
    Function GetFolderName(filespec As String) 'Returns path with trailing "\"
    'Requires GetFileName() function above
    GetFolderName = Left(filespec, Len(filespec) - Len(GetFileName(filespec)))
    End Function
    Function GetBaseName(filespec As String)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    GetBaseName = fso.GetBaseName(filespec)
    End Function[/vba]

  3. #3
    Hi Kenneth


    I understand where you are coming from but all the functions that you have suggested use "filespec" as the input file - which I am presuming is the zipped file that I am trying to unzip, right?

    Even if I get the filename and every other attribute associated with that zipped file, how do I get hold of the name of the files that are encapsulated in the zipped file.

    I have attached the modified Ron's code that I am trying to use to rename the files contained in the zipped file based on some condition on the name of this encapsulated file, but it is doing is just creating another folder under the zipped file with the same name as the zipped file (without the extension) and moving all the files from the zipped file to this folder. This is not what I am looking at doing.

    This is his code (modified)


    [vba]
    Sub Unzip1()
    Dim FSO As Object
    Dim oApp As Object
    Dim Fname As Variant
    Dim FileNameFolder As Variant
    Dim DefPath As String
    Dim strDate As String
    Dim fileNameInZip As Variant

    Fname = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", _
    MultiSelect:=False)
    If Fname = False Then
    'Do nothing
    Else
    'Root folder for the new folder.
    'You can also use DefPath = "C:\Users\Ron\test\"
    DefPath = "D:\MyTestFolder" 'Application.DefaultFilePath
    If Right(DefPath, 1) <> "\" Then
    DefPath = DefPath & "\"
    End If

    'Create the folder name
    'strDate = Format(Now, " dd-mm-yy h-mm-ss")
    FileNameFolder = DefPath ' & "MyUnzipFolder " & strDate & "\"

    'Make the normal folder in DefPath
    'MkDir FileNameFolder

    'Extract the files into the newly created folder
    Set oApp = CreateObject("Shell.Application")

    'oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items


    For Each fileNameInZip In oApp.Namespace(Fname).items
    If LCase(fileNameInZip) Like LCase("*.xlsx") Then
    oApp.Namespace(FileNameFolder).CopyHere _
    oApp.Namespace(Fname).items.Item(CStr(fileNameInZip))
    End If
    Next fileNameInZip


    'If you want to extract only one file you can use this:
    'oApp.Namespace(FileNameFolder).CopyHere _
    'oApp.Namespace(Fname).items.Item("test.txt")

    'MsgBox "You find the files here: " & FileNameFolder

    On Error Resume Next
    Set FSO = CreateObject("scripting.filesystemobject")
    FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True
    End If
    End Sub
    [/vba]


    Is there no other way of getting hold of the file that has just been unzipped using the command line statement to unzip the file as I have used in my original code something like this :-

    [vba]
    Sub UnZip_ZipFile(strZippedFileName As String, strTargetFolder As String, Optional strTargetFile As String)

    Const PATHWINZIP As String = "C:\progra~1\winzip\"
    Dim ShellStr As String

    ShellStr = PATHWINZIP & "Winzip32 -min -e -o" _
    & " " & Chr(34) & strZippedFileName & Chr(34) _
    & " " & Chr(34) & strTargetFolder & Chr(34)

    Shell ShellStr, vbHide

    'FileCopy strTargetFolder & Dir(strTargetFolder), strTargetFolder & strTargetFile ' & ".txt"

    End Sub
    [/vba]

    This subroutine gets called for each zipped file that is to be uncompressed and works perfectly ok except that I am not able to (because I don't know how to) rename the file as it is being uncompressed.

    I surely don't quite understand this concept of .Namespace() very well at all and some help in either being able to work with the individual files encapsulated within the zipped file would be highly appreciated.



    Deepak

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,954
    Location
    Let's see if I have this right. You want to rename all of the zip files contained in one zip file to have the same name as the file that it contains except for the file extension difference?

    Yes, filespec is just the input variable name for the filename in the routines that I posted. As you can see, it is handy to know how to get a file's basename, file extension and such.

  5. #5
    snb
    Guest
    You can retrieve the filenames with extension '.xls' in a zip file using:

    [vba]Sub Zipfiles()
    sn= zips1("G:\OF\test2012.zip")
    unzip_zipfile "G:\OF\test.zip", "G:\", sn(0), "2012")
    End Sub

    Function zips(c00,c01)
    Open c00 For Binary As #1
    sn = Split(Input(LOF(1), #1), "PK" & Chr(1) & Chr(2))
    Close #1

    For Each fl In sn
    if instr(fl,c01) then zips = zips & vbLf & Mid(Split(fl, "PK")(0), 43)
    Next
    End Function[/vba]

    [VBA]Sub UnZip_ZipFile(c01 As String, c02 As String, optional c03 As String, optional c04 as string)
    Shell replace("C:\progra~1\winzip\"Winzip32 -min -e -o ~" & c01 & "~ ~" & c02 "~","~",chr(34)), vbHide
    do
    do events
    loop until filelen(c02 & c03)>0
    name c02 & c03 As c02 & replace(c02,".xls", c04 & ".xls")
    End Sub[/VBA]
    Last edited by snb; 04-29-2012 at 10:24 AM.

  6. #6
    snb
    Guest
    Amendment:

    [VBA]
    Sub Zipfiles()
    sn= split(mid(zips1("G:\OF\test2012.zip"),2),vblf)
    unzip_zipfile "G:\OF\test.zip", "G:\", sn(0), "2012")
    End Sub

    [/VBA]

  7. #7
    Hi Kenneth

    No, that is not what I want. Just to make it clear, this is what I need to do :-

    Say, I have a set of ten(10) zip files titled like so - "Test1.zip", "Test2.zip" etc. ... "Test10.zip". All of these files are located in one single folder called "MyZippedFiles" on the "D:\" drive. Each of these zip files contain only one (1) file in this example but they can contain more than one (1), in fact any number of files with any extension. The names of the zip file and those of the files contained in the zip file bear no resemblance to each other.

    I want to run a VBA code that will unzip each of these files in the same folder that these zipped files are located in and rename them (the files that are contained in the zip file and not the zip file itself) as they are being unzipped to something that is more appropriate. So I don't want to do anything to the zip file except to unzip it and work with the files that are encapsulated in the zip file.


    I hope this makes it a trifle clearer.



    Best regards

    Deepak

  8. #8
    Hi SNB

    Sorry, mate but I don't understand all of what you have suggested. You will have to walk me through some of this :-

    In this code :-


    [vba]
    Sub UnZip_ZipFile(c01 As String, c02 As String, optional c03 As String, optional c04 as string)

    Shell replace("C:\progra~1\winzip\"Winzip32 -min -e -o ~" & c01 & "~ ~" & c02 "~","~",chr(34)), vbHide
    do
    do events
    loop until filelen(c02 & c03)>0
    name c02 & c03 As c02 & replace(c02,".xls", c04 & ".xls")
    End Sub


    [/vba]

    I take it that c01 and c02 are the zip filename and the target folder specification (as was used in my original code) and the variables c03 and c04 are the desired name and the desired extension for the file that is contained in the zip file, yeah? I haven't run this yet but I will soon.

    What does the "Do Events" statement do inside a loop?


    If that is the case, how is it going to work if there are more than one (1) encapsulated within the zip file?



    I don't follow this code - could you please explain what is happening here?

    Quote Originally Posted by snb

    [vba]
    Sub Zipfiles()
    sn= zips1("G:\OF\test2012.zip")
    unzip_zipfile "G:\OF\test.zip", "G:\", sn(0), "2012")
    End Sub

    Function zips(c00,c01)
    Open c00 For Binary As #1
    sn = Split(Input(LOF(1), #1), "PK" & Chr(1) & Chr(2))
    Close #1

    For Each fl In sn
    if instr(fl,c01) then zips = zips & vbLf & Mid(Split(fl, "PK")(0), 43)
    Next
    End Function[/vba]




    Best regards



    Deepak

  9. #9
    snb
    Guest
    You can create an inventory of all zipped files in a zip file using the function 'zips'.
    The result wil be returned as an 0-dimensional array.
    You can filter that array for any string; in this case the extension ".xls"

    After that you can use the sub 'unzip_zipfile' to unzip the first zipped Excel file: i.e. sn(0).
    The unzip_zipfile has 4 arguments:
    - the zipfile
    - the unzip directory
    - the file to unzip
    - the number that has to be used to rename the unzipped file

    The Doevents instruction you can find in the VBEditor's helpfiles.

    Since the VBA-code interpreter doesn't function correctly in this forum I will abstain from using code tags.

    Sub Zipfiles()


    sn= filter(zips("G:\OF\test2012.zip"),".xls") ' inventory fo all zipped Excel files
    if ubound(sn) > -1 then unzip_zipfile "G:\OF\test.zip", "G:\", sn(0), "2012")
    End Sub

    Function zips(c00)
    Open c00 For Binary As #1
    sn = Split(Input(LOF(1), #1), "PK" & Chr(1) & Chr(2))
    Close #1

    For Each fl In sn
    Then zips = zips & "|" & Mid(Split(fl, "PK")(0), 43)
    Next


    zips=split(mid(zips,2),"|")


    End Function



    Sub UnZip_ZipFile(c01 As String, c02 As String, c03 As String, c04 As String)


    Shell replace("C:\progra~1\winzip\"Winzip32 -min -e -o ~" & c01 & "~ ~" & c02 "~","~",chr(34)), vbHide

    Do

    DoEvents


    Loop Until filelen(c02 & c03)>0



    Name c02 & c03 As c02 & replace(c02,".xls", c04 & ".xls")
    End Sub





  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Quote Originally Posted by snb
    Since the VBA-code interpreter doesn't function correctly in this forum I will abstain from using code tags.
    Perhaps you would care to elaborate on that statement, for the benefit of those of us who use them all of the time.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    snb
    Guest
    See http://vbaexpress.com/forum/showpost...32&postcount=5

    I entered

    DoEvents

    the interpreter returns

    Do events

    Secondly, editing a post after posting it for the first time gives unpredictable results; even if you only post 'flat' text. E.g. my previous post. I typed all codelines on a separate code line. After editing it added many more empty lines into the post.

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    I will try and contact the code supplier re DoEvents and see if we can get that keyword parsed.

    I noticed that your code seems to get messed up re lines. I don't have that problem, so are you using soft returns, or coding it somewhere other than the VBIDE?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  13. #13
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,954
    Location
    I made a post in another forum to discuss VBA code tags. http://www.vbaexpress.com/forum/showthread.php?t=42018

    Deepak, if snb's solution does not fit, please explain your problem in more detail. If you make a zip file example and explain that, it might help. If you stick with this thread, you should get a solution.

  14. #14
    snb
    Guest
    @xld

    I only use the editor screens this forum provides.

    let try to illustrate:
    in this post I will post some VBA code

    in the next post I will post the same code; save the post and edit it afterwards.

    [vba]Sub snb()
    msgbox "this is only an example"

    open "G:\OF\example.txt for input as #1
    c01=input(LOF(1),1)
    close #1
    end sub[/vba]

  15. #15
    snb
    Guest
    After saving this code it appeared that the line c01=.... has not been indented.
    Now I am editing this post and will post the changes.

    [vba]Sub snb()
    msgbox "this is only an example"

    open "G:\OF\example.txt for input as #1
    c01=input(LOF(1),1)
    close #1
    end sub[/vba]

  16. #16
    snb
    Guest
    Now I copy/paste previous code, meant to contian no empty rows except between procedures:

    Sub Zipfiles()



    sn= filter(zips("G:\OF\test2012.zip"),".xls") ' inventory fo all zipped Excel files

    if ubound(sn) > -1 then unzip_zipfile "G:\OF\test.zip", "G:\", sn(0), "2012")

    End Sub

    Function zips(c00)
    Open c00 For Binary As #1
    sn = Split(Input(LOF(1), #1), "PK" & Chr(1) & Chr(2))
    Close #1

    For Each fl In sn
    Then zips = zips & "|" & Mid(Split(fl, "PK")(0), 43)
    Next






    zips=split(mid(zips,2),"|")

    End Function


    Sub UnZip_ZipFile(c01 As String, c02 As String, c03 As String, c04 As String)

    Shell replace("C:\progra~1\winzip\"Winzip32 -min -e -o ~" & c01 & "~ ~" & c02 "~","~",chr(34)), vbHide


    Do
    DoEvents

    Loop Until filelen(c02 & c03)>0

    Name c02 & c03 As c02 & replace(c02,".xls", c04 & ".xls")


    End Sub


Posting Permissions

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