Rename files at unzip!

[agarwaldvk]

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

[Kenneth Hobs]

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]

[agarwaldvk]

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

[Kenneth Hobs]

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.

[snb]

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]

[snb]

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]

[agarwaldvk]

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

[agarwaldvk]

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?

[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

[snb]

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

[Bob Phillips]

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.

[snb]

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.

[Bob Phillips]

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?

[Kenneth Hobs]

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.

[snb]

@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]

[snb]

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]

[snb]

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