Option Compare Database
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
'Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'for 64 bit
Sub ZipAllFilesInFolder(zippedFileFullName As String, folderToZipPath As String)
Dim ShellApplication As Object
Dim fso As Object
Dim destinationFolder As Object
Dim sourceFolder As Object
Set ShellApplication = CreateObject("Shell.Application")
'Check for zipFolder has created
If Dir(zippedFileFullName, vbDirectory) = "" Then
MkDir zippedFileFullName
End If
' Create an empty zip file
Open zippedFileFullName For Output As #1
Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
Close #1
' Add items to the zip file
With ShellApplication
Debug.Print Timer, "zipping started ..."
.NameSpace(CVar(zippedFileFullName)).CopyHere CVar(folderToZipPath)
On Error Resume Next
Do Until .NameSpace(CVar(zippedFileFullName)).Items.Count = 1
'DoEvents
Sleep 100
Debug.Print " .";
Loop
Debug.Print
On Error GoTo 0
Debug.Print Timer, "Zip done."
End With
' Clean up
Set ShellApplication = Nothing
End Sub
'call sub macro
Sub Test_ZipAllSubfoldersInFolder()
' Allow user to select parent folder
Dim parentFolder As String
Dim folderPicker As FileDialog
Set folderPicker = Application.FileDialog(msoFileDialogFolderPicker)
With folderPicker
.Title = "Select Parent Folder"
.AllowMultiSelect = False
If .Show = -1 Then
parentFolder = .SelectedItems(1)
Else
MsgBox "No folder selected, operation cancelled"
Exit Sub
End If
End With
' Get the file system object
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim parentFolderObj As Object
Set parentFolderObj = fso.GetFolder(parentFolder)
' Loop through each subfolder
Dim subFolder As Object
For Each subFolder In parentFolderObj.SubFolders
Dim zipFileName As String
zipFileName = parentFolder & "\" & subFolder.Name & ".zip"
ZipAllFilesInFolder zipFileName, subFolder.Path
Next subFolder
End Sub
Bookmarks