Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Issues Zipping Files in Access 2007 1

Status
Not open for further replies.

MikeC14081972

Programmer
May 31, 2006
137
GB
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)

Code:
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

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
 
Getting closer

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

Code:
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
Code:
fso.CopyFile Attach(i), fName

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

Thanks
 
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
 
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:
Code:
oApp.namespace([red]([/red]fpath[red])[/red]).CopyHere Attach(i)

The only other difference I have is a delay:
Code:
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top