• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Unzip Files

M

motabrasil

Member
Hi There
I have a challenge today and this is related to:
How can we UNZIP csv. files and convert them into an Excel files in the same folder?
I have a Folder with several files. One of them resides my macro and another one is a ZIP file with around ten CSV.files inside. My need is: when I run my macro, I would like to have this macro opening the zip file and convert all files found in Excel files and save them into the same macro folder. This operation repeat every day and the ZIP.file name change every day as well (i.e: detailReports_113_9_16_8_54_18). Any idea?????

Hope to have your precious support.
Thanks in advance
Motabrasil
 
Hi !​
​
First of all, you have to dissociate your needs …​
​
Start with .csv files (can have differents delimiters and differents end of line sequences)​
by using the Macro Recorder to open it. Then test the generated code …​
Many examples on the net …​
​
Once .csv file code is well working, search for example on this site "Built-in Zip" …​
 
I have the code described below which is working almost perfect. The only thing I want to change is
the code ask me to browse the file instead of open it automatically.
What should I change in my code to have it opening my Zip file automatically?

Thanks in advance

*****

Sub UnzipFiles()
Dim ws As Worksheet
Dim StartRow As Integer, fn As Variant, f As Integer, s As Integer
Dim wbOpen As Workbook
Dim name As String, StrFile As String
currentWK = ActiveWorkbook.name
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
StartRow = 1

Set wbOpen = ActiveWorkbook
name = ActiveWorkbook.name
directory = ActiveWorkbook.Path
fn = Application.GetOpenFilename("directory,*detailreports*.zip", 1, "Multiple Selection", , True)
If TypeName(fn) = "Boolean" Then Exit Sub
For f = 1 To UBound(fn)
DefPath = Left(fn(1), InStrRev(fn(1), "\"))
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If
FileNameFolder = DefPath & "TempUnzipFolder\"
MkDir FileNameFolder
Set oApp = CreateObject("Shell.Application")

oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(fn(f)).items

StrFile = Dir(FileNameFolder)
Do While Len(StrFile) > 0
Set wb = Workbooks.Open(FileNameFolder & StrFile)
With wb
.SaveAs Replace(Replace(wb.FullName, "TempUnzipFolder\", ""), ".csv", ".xlsx")
.Close True
End With
Set wb = Nothing
StrFile = Dir
Loop

On Error Resume Next
DeleteFolder (FileNameFolder)

Next
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
MsgBox ("Macro completed successfuly")
End Sub

.....

Sub DeleteFolder(MyPath As String)
Dim FSO As Object
Set FSO = CreateObject("scripting.filesystemobject")
If Right(MyPath, 1) = "\" Then
MyPath = Left(MyPath, Len(MyPath) - 1)
End If
If FSO.FolderExists(MyPath) = False Then
MsgBox MyPath & " doesn't exist"
Exit Sub
End If
FSO.DeleteFolder MyPath
End Sub
 
Hi, motabrasil!

You have an issue at this line:
fn = Application.GetOpenFilename("directory,*detailreports*.zip", 1, "Multiple Selection", , True)

Arguments for GetOpenFilename method are:
FileFilter, Optional, Variant, string to specify filtering criteria
FilterIndex, Optional, Variant, number from 1 to FileFilter count
Title, Optional, Variant, dialog title
ButtonText, Optional, Variant, only available in Mac versions
MultiSelect, Optional, Variant, boolean true or false

Specifically for the 1st argument, FileFilter, you should clarify what did you try to mean with:
"directory,*detailreports*.zip".

As a blind shot, tried yet omitting "directory," (unquoted)?

Regards!
 
Hi Ninja,
Actually "directory" is the path from the workbook where I save my Macro and "detailReports" is the Initial Name from my ZIP file. The ZIP file name changed every time I run the report from SAP but the initial name doesn't change.
I was trying to address my code to capture this ZIP file from the same folder where my macro resides without browsing it.

If you have any idea how to change my code to make it works I would appreciate.

Thanks
 
Hi, motabrasil!

The 1st parameter should be a string for filtering the search, so you should do something like this:
a) In the declaration area add this line:
Dim sFileFilter as String, sCurDir as String
b) If the compressed files are in a different folder than the actual workbook, before the statement of the issue add this line:
If CurDir <> "proper path" then sCurDir = CurDir : ChDir "proper path"
c) Change the fn line to:
fn = Application.GetOpenFilename("Any text you want (*.zip), *.zip", 1, "Multiple Selection", , True)
d) Add this line after previous one:
If sCurDir <> "" And CurDir <> sCurDir Then ChDir sCurDir

Regards!
 
Hi Ninja

The Code is working fine but still Pop-up the browse to select the zip file instead of open it automatically
(See attached).
How to force the code to select and open the zip file automatically?
The Macro and the ZIP file will be always in the same.

Thanks once again
Motabrasil
 

Attachments

  • Browse Pop-Up.PNG
    Browse Pop-Up.PNG
    36.7 KB · Views: 4
Hi, motabrasil!
I understood that you wanted to browse for .zip files into a certain folder, that's what GetOpenFilename does. If you want to retrieve all the .zip files or just the 1st, or any ones that you could select by code (I didn't analyzed your posted code), why not use the Dir function if you know the path and the name/s?
Regards!
PS: Se eu não entendi, então eu acho que o meu Português deve ser melhor do que o meu Inglês, eu deveria mudar o idioma praticado no verão? (If I misunderstood, then I think my Portuguese should be better than my English, should I change the language practiced in the summer?)
 
Hi Ninja
Vc entendeu muito bem!!!! (You got it!!!) Your Portuguese is perfect!
My problem is, I have a huge code running with several reports and one of them comes as ZIP.
The report that comes as ZIP has a different name every time I run it but it resides in the same folder
as resides the macro. I don't want to waiting the code ask me to browse this ZIP report every time.
My need is the code identify the ZIP file from the current folder and select it and open it from them
automatically.The rest will be done perfectly.

Sorry for my bad English. Hope to be clear now
Thanks in advance
Motabrasil
 
Hi, motabrasil!
It's the only .zip file in that folder? If yes, we got it; if not, how do you identify it?
Regards!
PS: Years of summer practice at your beachs :p
 
Yes. Just one ZIP file per folder. The Initial name (in bold) is the same and the numeric portion
vary (i.e: detailReports_113_9_3_8_34_0)
PS.: Nice beaches over there.
 
Hi, motabrasil!
Try replacing this:
Code: 
fn = Application.GetOpenFilename("directory,*detailreports*.zip", 1, "Multiple Selection", , True)
If TypeName(fn) = "Boolean" Then Exit Sub
by this:
Code: 
    fn = Dir(directory & Application.PathSeparator & "detailReports*.zip")
    If fn = "" Then Exit Sub
    fn = directory & Application.PathSeparator & fn
Regards!
 
I replaced and I could see the code captured the file but stop in the loop For
(For f = 1 To UBound(fn)) with "Run-time error '13': Type mismatch
 
Hi, motabrasil!
You forced me to read the whooole code, that's not fair, know-it.
You defined fn as variant, and previously when assigned as the result of the GetOpenFilename method, it could be treated as an array, ergo turning valid the UBound function.
But if as we agreed upwards there will be only one .zip file in the specified folder with a name structure as detailReports*.zip, then with the suggested change of using Dir function, you don't need any more to loop thru the formerly array fn from 1st to last (its UBound) indexes: you directly have the name of the wanted file as result of the Dir instruction, that's why if it exists (non blank) in the next sentence the full path is build putting the value of "directory" variable and the backslash.
So you can get rid of the For...Next loop and use directly the string value stored in the fn variable.
I hope I made myself clear, otherwise are you tempting me to switch to carioca? But I'm better at paulista, thanks to my cousins.
Regards!
PS: Carioca, from Rio de Janeiro; paulista, from São Paulo (two cities of Brazil, just in case).
 
Why not just set up a batch file to run 7zip from the command line and rename the out files as .xls?
 
Hi Ninja
Won't be necessary to use your "Carioca" or "Paulista" at that time. I got you.
The code is working great now.
You rock once again!!!

Thanks
MotaBrasil
 
Hi, motabrasil!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
PS: Onsite support available from Dec-Mar, just 2 air tickets and... if 0km <= abs(site-beach) <= 5km :p
 
You must log in or register to reply here.
Back
Top