vba code to select file and unzip files in a different folder with different name

karthikha0706

New Member
Joined
Oct 30, 2013
Messages
44
HI All, Am looking for a vba code to select a file from a folder and unzip the file in a different folder with different file name. Can you please assist me with the code.
Thanks in advance.
 

karthikha0706

New Member
Joined
Oct 30, 2013
Messages
44
A VBA demonstration for starters :​
VBA Code:
Sub DemoUnZip1()
      Const D = "C:\XYZ\", S = "C:\Download\", C = ".csv"
        Dim Z$, oSh As Object, F$, N$
    With CreateObject("Scripting.FileSystemObject")
            Z = Dir$(S & "*.zip"):  If Z = "" Or Not .FolderExists(D) Then Beep: Exit Sub
            Set oSh = CreateObject("Shell.Application")
        Do
               F = oSh.NameSpace(S & Z).Items.Item(0).Name
            If F Like "???######" & C Then
                oSh.NameSpace(D).CopyHere oSh.NameSpace(S & Z).ParseName(F), 16
                N = D & Left$(F, 3) & C:  If .FileExists(N) Then .DeleteFile N, True
               .MoveFile D & F, N
               .DeleteFile S & Z, True
            End If
                   Z = Dir$
        Loop Until Z = ""
             Set oSh = Nothing
    End With
End Sub
Hi Marc. Its not working....its checking "IF" condition and coming out....
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,145
Office Version
  1. 2010
Platform
  1. Windows
As it well works on my side so the wrong is on yours …​
 

karthikha0706

New Member
Joined
Oct 30, 2013
Messages
44
As it well works on my side so the wrong is on yours …​
Hi Marc , I have below 3 files in the download folder and my XYZ folder is empty. Please correct me where i went wrong,

1625827526699.png
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,145
Office Version
  1. 2010
Platform
  1. Windows
As my VBA demonstration rocks on my side so you have badly explain / answer but as after all we are not on a mind readers forum​
so follow the VBA procedure in debug step-by-step mode - hitting F8 key - to find out where your logic fails …​
Another point : post a screenshot of the content of a zip file like for example ABC080721.zip
or if you can link it on a files host website like Dropbox for example …​
 
Last edited:
Solution

karthikha0706

New Member
Joined
Oct 30, 2013
Messages
44

ADVERTISEMENT

As my VBA demonstration rocks on my side so you have badly explain / answer but as after all we are not on a mind readers forum​
so follow the VBA procedure in debug step-by-step mode - hitting F8 key - to find out where your logic fails …​
Another point : post a screenshot of the content of a zip file like for example ABC080721.zip
or if you can link it on a files host website like Dropbox for example …​
Thanks a lot Marc. Its working fine. Can you please assist me with a code to rename a CSV file that starts with "AB" to purchase.csv. Happy Weekend !!!
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,145
Office Version
  1. 2010
Platform
  1. Windows
Like in post #3 code you can use the Name VBA statement …​
 

karthikha0706

New Member
Joined
Oct 30, 2013
Messages
44
Like in post #3 code you can use the Name VBA statement …​
Option Explicit

Sub UnzipAFile(zippedFileFullName As Variant, unzipToPath As Variant)

Dim ShellApp As Object

'Copy the files & folders from the zip into a folder
Set ShellApp = CreateObject("Shell.Application")
ShellApp.Namespace(unzipToPath).CopyHere ShellApp.Namespace(zippedFileFullName).items

End Sub


'To call the procedure above the following code can be used within another procedure. Change the paths to be the name of the zip file you wish to unzip and the folder you wish to put the unzipped files into.
Sub UnzipFileToFolder()
Call UnzipAFile("D:\Daily Prices\Pricing Strategy\purchase.zip", "D:\Daily Prices\Pricing Strategy\")
Name "D:\Daily Prices\Pricing Strategy\pu*.csv" As "D:\Daily Prices\Pricing Strategy\purchase.csv"


HI Marc, on the above code assist me on how to use wild card and rename a csv file that starts with "PU" to purchase.csv
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,145
Office Version
  1. 2010
Platform
  1. Windows
To find out a file with a wild card use first the Dir VBA function like in my demonstration​
so once the file found with Dir then you can rename it via the Name statement … See samples for both functions in VBA help.​
 

Forum statistics

Threads
1,136,737
Messages
5,677,468
Members
419,693
Latest member
divtjd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top