Good Morning All,
I hope everyone is well.
I'm using Ron De Bruin code (
) for zipping a folder and it's contents but I cannot seem to get it to work. Any help would be greatly appreciated! The folder is located within a server rather than my own documents - I changed the path to my documents and I cannot get it to work from there either.
The code creates the zip folder but it doesn't copy the document / documents in (only trying with one document at the minute but will be about 10 Excel files)
Sub zip_file_test()
Dim source, zipfile As String
Set wb = Workbooks("TF email testers.xlsm")
Set ws = wb.Worksheets("Email")
Set ref = ws.Range("N2")
source = "\\TF\" & ref & " - " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "MMM-YY")
zipfile = "\\TF\" & ref & " - " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "MMM-YY") & ".zip"
CreateZipFile source, zipfile
End Sub
Sub CreateZipFile(folderToZipPath As Variant, zippedFileFullName As Variant)
Dim ShellApp As Object 'shellapp, is the file copying over?
'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
'create an empty zip file
Set ShellApp = CreateObject("Shell.Application")
ShellApp.Namespace(zippedFileFullName).CopyHere ShellApp.Namespace(folderToZipPath).Items 'code fails here "Object Variable or With block variable not set"
On Error Resume Next
Do Until ShellApp.Namespace(zippedFileFullName).Items.Count = ShellApp.Namespace(folderToZipPath).Items.Count
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0
End Sub
Bookmarks