Issues Zipping Files in Access 2007 1

[MikeC14081972]

Hi,

I have been using the below code successfully for a while in Access 2003, however I'm unable to get it to run in 2007 (Code example has been cut down to save space)

Function Zip(fPath, ToAttach As String)
Dim oApp As Object
Dim Attach As Variant
Dim i As Integer

    CreateZip (fPath)

    Set oApp = CreateObject("Shell.Application")
 
    Attach = Split(ToAttach, ";")
    
    For i = LBound(Attach) To UBound(Attach) - 1
        
        oApp.Namespace(fPath).CopyHere Attach(i)
    Next i
    Set oApp = Nothing

End Function

Function CreateZip(zPath)

Dim fso As FileSystemObject
Dim arrHex, sBin, i

On Error GoTo ErrHandler

    Set fso = CreateObject("Scripting.FileSystemObject")

    arrHex = Array(80, 75, 5, 6, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

    For i = 0 To UBound(arrHex)
        sBin = sBin & Chr(arrHex(i))
    Next

    With fso.CreateTextFile(zPath, True)
        .Write sBin
        .Close
    End With

exit function

The code will run and create the required zip folder but will not move files to the folder and I'm receiving the following error

Run-time error '91':
Object variable or with block not set

on the following line of code

oApp.Namespace(fPath).CopyHere Attach(i)

I can add files manually to the zip folder so I know the issue doesn't lie there.

Has anyone got any ideas why this would be happening?

Thanks in Advance

 

[MikeC14081972]

Getting closer

Namespace wont recognize a string variable so change the line that errors to

oApp.namespace((fpath)).CopyHere Attach(i)

Code now runs through without an error. However, on looking at the zip file there are no files enclosed so the .CopyHere is working correctly.

Any Ideas?????

if I use

fso.CopyFile Attach(i), fName

The files are copied over but the zip file is corrupted when you try and open.

Thanks

 

[henniec]

Hi.

I received this one line fuction several years ago to zip a file.

If you can export your table to a file using something similar to the first function and then use the second function to export it to a zip file.

I export the table "sample" to an XML file and then to a zipfile.

Application.ExportXML acExportTable, "sample", "H:\Chem\DATA\XMLReport\sample.xml", , , , acUTF8, 1

Shell "C:\Program Files\WinZip\WINZIP32.EXE" & " -a " & "Filename.zip" & _
" " & "Filename to be zipped", vbNormalFocus

I hope this helps.

Hennie

 

[traingamer]

Try adding a short delay to the process. I used something similar in 2003 which failed in 2007, but I fixed it by adding the parenthesis like you did here:

oApp.namespace([red]([/red]fpath[red])[/red]).CopyHere Attach(i)

The only other difference I have is a delay:

Option Compare Database
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)



...




    For i = LBound(Attach) To UBound(Attach) - 1
        
        oApp.namespace((fpath)).CopyHere Attach(i)
        lngFilesAdded = lngFilesAdded + 1

    Do Until oApp.namespace((fpath)).Items.Count >= [blue]lngFilesAdded[/blue]
      Sleep 1000
    Loop
    Next i

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[MikeC14081972]

Sorry for the late thanks Greg.

I removed my wait and replaced it by using the sleep API and it all works a treat now.