Naresh Patel
asked on
ExcelVBA Which Upzip Zip Files
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
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
ASKER
sir,
I had made some change for just testing purpose in code
but every time I run this code it unzip only 1 file and message pop up.
actually want to convert all zip file instead of 1 file in that directory.
Thanks
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"
but every time I run this code it unzip only 1 file and message pop up.
actually want to convert all zip file instead of 1 file in that directory.
Thanks
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
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
ASKER
"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
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
ok I will give you the code I am using which is the same as before. Here it is
gowflow
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
gowflow
pls copy paste the code you have in this workbook or simply attach it.
gowflow
gowflow
ASKER
Extremely sorry out of the desk for few hours .....Sure l will as soon as I reached to my desk.
Thanks
Thanks
ok
gowflow
gowflow
ASKER
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
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
ASKER
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
Thanks
cm04DEC2012bhav.csv.zip
cm04JAN2012bhav.csv.zip
cm04JUL2012bhav.csv.zip
cm04JUN2012bhav.csv.zip
cm04MAY2012bhav.csv.zip
ASKER
I wanted to ask you to upload some sample files good you did let me check will revert.
gowflow
gowflow
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome
ASKER
just need to let you know if I run in this situation
I closed this question as I got it what I asked for .
Thanks
'sOFolder = "D:\Data\NSE Downloads\Equity"
'sDFolder = "C:\Data\NSEeq"
sOFolder = ActiveWorkbook.Path
sDFolder = ActiveWorkbook.Path & "\" & "Unzip"
first time when Unzip Folder is not found then it pops up 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
ASKER
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
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
ASKER
yes no error message but 1st click it unzip 1 file & in 2nd click it unzip all.
Thanks
Thanks
... 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
gowflow
ASKER
Sure Sir.
ASKER
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