Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ExcelVBA Which Upzip Zip Files

Posted on 2014-11-05
25
Medium Priority
?
170 Views
Last Modified: 2014-11-12
Hi Experts,

Need piece of code which unzip zip files from  directory "D:\Data\NSE Downloads\Equity"and its sub directory & places all zip files to directory "D:\Data\NSEeq" and delete all unzip files from directory and its sub directory.

Attached is sample Zip file which I need to unzip via excel VBA.

Thanks
cm01APR2013bhav.csv.zip
0
Comment
Question by:Naresh Patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 11
25 Comments
 
LVL 31

Expert Comment

by:gowflow
ID: 40425708
Here is the code for that

Option Explicit

Sub UnzipAllFiles()
Dim zFile As String
Dim sOFolder As String
Dim sDFolder As String

sOFolder = "D:\Data\NSE Downloads\Equity"
sDFolder = "C:\Data\NSEeq"
'sOFolder = ActiveWorkbook.Path
'sDFolder = ActiveWorkbook.Path & "\" & "Unzip"


Do
    zFile = Dir(sOFolder & "\*.zip")
    If zFile <> "" Then
         UnzipFile sOFolder & "\" & zFile, sDFolder & "\"
         zFile = Dir("")
    End If
Loop Until zFile = ""

MsgBox "Files Unzipped to  : " & sDFolder

End Sub

Sub UnzipFile(ByVal sZipFile As String, ByVal sDestFolder As String)

Dim objApp As Object
Dim objArchive As Object
Dim objDest As Object
Dim vDestFolder As Variant
Dim vZipFile As Variant

Set objApp = CreateObject("Shell.Application")

vZipFile = sZipFile
vDestFolder = sDestFolder

If Dir$(sDestFolder, vbDirectory) = "" Then MkDir sDestFolder

objApp.Namespace(vDestFolder).CopyHere objApp.Namespace(vZipFile).Items
Kill sZipFile

End Sub

Open in new window



Simply put this in a workbook and run the Sub UnzipAllFiles
First make a copy of the folder to ensure that no problem especially that it delete the Original Zip files as you requested.

gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40427633
sir,

I had made some change for just testing purpose in code
'sOFolder = "D:\Data\NSE Downloads\Equity"
'sDFolder = "C:\Data\NSEeq"
sOFolder = ActiveWorkbook.Path
sDFolder = ActiveWorkbook.Path & "\" & "Unzip"

Open in new window


but every time I run this code it unzip only 1 file and message pop uppopup message.
actually want to convert all zip file instead of 1 file in that directory.

Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40427696
I have zip files like this.zip files
Thanks
0
The Ideal Solution for Multi-Display Applications

Check out ATEN’s VS1912 12-Port DP Video Wall Media Player at InfoComm 2017. Kerri describes how easy it is to design creative video walls in asymmetric layouts and schedule detailed playlists ahead of time with its advanced scheduling feature.

 
LVL 31

Expert Comment

by:gowflow
ID: 40427828
I see they are all DEC files are they named the same like 31 28 27 etc... or inside they are all named DEC
Pls chk this out as I tested it with multiple zip files and it go thru all of them correctly.

I even copied the file you posted and gave it several copies and changed the file inside to be different and ran the macro and all is fine.
gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40427858
"I see they are all DEC files are they named the same like 31 28 27 etc... or inside they are all named DEC "
yes it is named like 31 28 27 not all named DEC.

"Pls chk this out as I tested it with multiple zip files and it go thru all of them correctly."
Do you mean you attached new code or file ? If yes then I don't see any attchment.


Thank You
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40427864
ok I will give you the code I am using which is the same as before. Here it is

Option Explicit

Sub UnzipAllFiles()
Dim zFile As String
Dim sOFolder As String
Dim sDFolder As String

'sOFolder = "D:\Data\NSE Downloads\Equity"
'sDFolder = "C:\Data\NSEeq"
sOFolder = ActiveWorkbook.Path
sDFolder = ActiveWorkbook.Path & "\" & "Unzip"


Do
    zFile = Dir(sOFolder & "\*.zip")
    If zFile <> "" Then
         UnzipFile sOFolder & "\" & zFile, sDFolder & "\"
         zFile = Dir("")
    End If
Loop Until zFile = ""

MsgBox "Files Unzipped to  : " & sDFolder

End Sub

Sub UnzipFile(ByVal sZipFile As String, ByVal sDestFolder As String)

Dim objApp As Object
Dim objArchive As Object
Dim objDest As Object
Dim vDestFolder As Variant
Dim vZipFile As Variant

Set objApp = CreateObject("Shell.Application")

vZipFile = sZipFile
vDestFolder = sDestFolder

If Dir$(sDestFolder, vbDirectory) = "" Then MkDir sDestFolder

objApp.Namespace(vDestFolder).CopyHere objApp.Namespace(vZipFile).Items
Kill sZipFile

End Sub

Open in new window


gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40427945
here is what happens in mine side after applying above code.Acitve WB Location - Button which trigger macroUnzip Folder created by macroThis is unzip folder where only one file is available.Thanks
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40428227
pls copy paste the code you have in this workbook or simply attach it.
gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40428414
Extremely sorry out of the desk for few hours .....Sure l will as soon as I reached to my desk.

Thanks
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40428428
ok
gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40429184
Sir. Gowflow,

sorry for delay as one of mine worker is in hospital so little busy.

investigated code and conclusion - working perfect but each time after execute it unzip only one file ....need to unzip all at once i.e. one click and all file unzip.

attached is mine WB


Thanks
Zip-Test.xlsm
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40429485
Try this
gowflow
Zip-Test.xlsm
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40430341
Same result attaching 5 .zip files  ...you just download & run macro ...it will only unzip one file on one click. require to one click and all unzip ..

Thanks
cm04DEC2012bhav.csv.zip
cm04JAN2012bhav.csv.zip
cm04JUL2012bhav.csv.zip
cm04JUN2012bhav.csv.zip
cm04MAY2012bhav.csv.zip
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40430346
Is my laptop processor create problem? I find it is slow but I am not sure ? if that is the case then pls let me know.PropertiesThanks
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40430484
I wanted to ask you to upload some sample files good you did let me check will revert.
gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40430487
Tough luck !!
I ran it with the exact same file I last posted to you and pressed on the button you created and here is the result.

Result of unzip
Sorry no clue Except !!!! Windows 8 that does not like the command Dir and it is very possible,

Let me do some research.
gowflow
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 40430517
Some weird behavior ...

Try this version
gowflow
Zip-Test-V01.xlsm
0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 40430770
Awesome
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40430771
just need to let you know if I run in this situation  
'sOFolder = "D:\Data\NSE Downloads\Equity"
'sDFolder = "C:\Data\NSEeq"
sOFolder = ActiveWorkbook.Path
sDFolder = ActiveWorkbook.Path & "\" & "Unzip"

Open in new window

first  time when Unzip Folder is not found then it pops up error Unzip folder not there so error after this second time all worked well and if unzip folder is there then no error.

I closed this question as I got it what I asked for .

Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40430775
Sir. Gowflow,

last two question in this line. one is this one. & last is to combine all in one WB.

Please if you have time  help me on this.

Thanks
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40430973
this should fix it for the Error.
gowflow
Zip-Test-V02.xlsm
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40431885
yes no error message but 1st click it unzip 1 file & in 2nd click it unzip all.

Thanks
0
 
LVL 31

Expert Comment

by:gowflow
ID: 40432079
... well sorry if I cannot reproduce the error then it become a difficult issue to deal with. I guess you can live with this.
gowflow
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40432112
Sure Sir.
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 40437005
here is follow up question ....please help me out Combine .csv

Thanks
0

Featured Post

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question