How do i correct the code to create a zip file

[henniec]

Good day,

I am trying to create a zip file for an XML file created by Access. This will be the final step in reporting data extracted from the database before it would be uploaded to a website. I found some code written by Ron de bruin (

http://www.rondebruin.nl/windowsxpzip.htm)

for Excel and I am trying to modify it for use in Access. I need it to create the folder and the zip file.

There is no errors up to “Call Shell("C:\Program Files\WinZip\WINZIP32.EXE", 1)” but with the next step I receive the error “Active X component can’t create object”. I do have Microsoft Active X Data Objects Library 2.8 referenced. As I cannot get passed the above step I do not know if my other change is correct.

I only have one file to zip. The name will change frequently but will take care of it in code. The zip file folder will be called Lennox.zip and the path for both the zipfile/folder is “H:\Chem\DATA\XML Report” and the name of the file to be zipped is “Q3-2007.xml”.

Any help would be appreciated.

Thank you.

Hennie

Sub Zip_All_Files_in_Folder_Browse()
Dim FileNameZip, FolderName, oFolder
Dim strDate As String, DefPath As String
Dim oApp As Object, strQtr As String

DefPath = "C:\Program Files\WinZip\ "
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

strDate = Format(Now, " dd-mmm-yy h-mm-ss")
FileNameZip = DefPath & "lennox" & ".zip" '& strDate

Call Shell("C:\Program Files\WinZip\WINZIP32.EXE", 1)

Original coding for the next few lines.

FileNameZip = DefPath & "MyFilesZip " & strDate & ".zip"

Set oApp = CreateObject("Shell.Application")

'Browse to the folder
Set oFolder = oApp.BrowseForFolder(0, "Select folder to Zip", 512)

My modification
Set oApp = CreateObject("Shell.C:\Program Files\WinZip\WINZIP32.EXE")
Tried the above step without “shell”. No difference.

'Browse to the folder
Set oFolder = oApp.BrowseForFolder(1, "H:\Chem\DATA\XML Report\", 512)

If Not oFolder Is Nothing Then
'Create empty Zip File
NewZip (FileNameZip)

FolderName = oFolder.Self.Path
If Right(FolderName, 1) <> "\" Then
FolderName = FolderName & "\"
End If

'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

MsgBox "You find the zipfile here: " & FileNameZip
On Error GoTo 0

Set oApp = Nothing
Set oFolder = Nothing
End If
End Sub

 

[ck1999]

I read through the link and do not understand where you are getting that call line from.

Try removing the call and your modification and see if it works. It seems to me you are combining the winzip method and the windowsxp zip method.

ck1999

 

[mikej336]

Yes, Ron de bruin's example uses windows xp zip, not winzip.

You are mixing 2 different things. If you are using xp, go back to Ron de bruin original example.

Otherwise, if you have a lisensed copy of winzip you might want to look at the free command line add in.


Mike

 

[henniec]

Thank you for your replies. I am using XP and I do agree that I may be mixing the two.

I were not even aware there is XP zip or where to find it or how it works.

Mike you refer to an free add in for winzip. I found some information about it and will be going that route. Far easier and less complicated.

Thank you to both of you for your input.

Hennie

 

[oharab]

Hennie,
It sounds like you're sorted, but if it doesn't work, there's an example on my download page for using the free Info-Zip dll's to create and extract from archives. It's very quick & easy & means you can use the database on another machine without having to have winzip installed on it.

Just my 2ps worth.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[henniec]

Thanks Ben,

That add-in line works perfectly. Thats all I needed and its much less work than the code I posted.

regards.

Hennie