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