Zipping Files

[luckee]

Hi, want to make some changes to the below in red so it picks up the information from the active sheet rather than changing in the code.

Zip file(s) with the default Windows zip program (VBA)

Sub Zip_All_Files_in_Folder()
Dim FileNameZip, FolderName
Dim strDate As String, DefPath As String
Dim oApp As Object

DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

FolderName = "C:\Users\Ron\test\" '<< Change 1. How to change this so it can reference the path on Sheet1 cell A1?

strDate = Format(Now, " dd-mmm-yy h-mm-ss")
FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip" 2. How to change this so it can pick up the file name from Sheet1 cell A2?

'Create empty Zip File
NewZip (FileNameZip)

Set oApp = CreateObject("Shell.Application")
'Copy the files to the compressed folder
oApp.Namespace(FileNameZip).CopyHere oApp.Namespace(FolderName).items

'Keep script waiting until Compressing is done
On Error Resume Next
Do Until oApp.Namespace(FileNameZip).items.Count = _
oApp.Namespace(FolderName).items.Count
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0

MsgBox "You find the zipfile here: " & FileNameZip
End Sub

Thank you.

 

[Domenic]

Did you check for illegal characters, as I suggested in my previous post?

 

[luckee]

Did you check for illegal characters, as I suggested in my previous post?

Sorry, I got it now. Thanks for your help!

 

[luckee]

Did you check for illegal characters, as I suggested in my previous post?

One more question - the completed zip file is saved to My Documents. is there a way to have it save in the same path as where the files being zipped were?

 

[Domenic]

Try the following...

FolderName = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value

If Right(FolderName, 1) <> "\" Then
    FolderName = FolderName & "\"
End If

FileNameZip = FolderName & ThisWorkbook.Worksheets("Sheet1").Range("A2").Value

Notice that this time we're making sure that the string assigned to FolderName ends in a backslash ( \ ), since it's now being used as the path for the zipped file.

Hope this helps!