• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

Zipping a folder in Access 2003 using VBA

Hi experts,

I would like to zip up a folder on my C:\ drive. The folder is just a normal folder that you find in the C:\drive.

I had this code from a couple of years back I think, which came from Experts Exchange.  It works, but only if I am zipping up an MDB.  

Please could somebody help me with the code to so that it zips up the whole folder?

Thank you.

Terry
Private Sub Command0_Click()
Set objFSO = CreateObject("Scripting.FileSystemObject") 'Create instance of FSO

Compress ("C:\FolderA")
Set objFSO = Nothing
End Sub


Sub Compress(fil)
    Dim strPath, strExt, strfile As String

    strPath = Left(fil, InStrRev(fil, "\"))     'Acquire Path
    strfile = Mid(fil, InStrRev(fil, "\") + 1)     'Acquire filename
    strExt = Mid(strfile, InStrRev(strfile, ".") + 1, 3)     'Acquire extension
    Dim strZip As String
    strZip = strPath & Replace(strfile, "mdb", "zip")

    Dim zipFil As Object

    Set zipFil = objFSO.CreateTextFile(strZip)
    zipFil.WriteLine Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, 0)
    zipFil.Close

    Dim oApp As Object


    Set oApp = CreateObject("Shell.Application")
    'oApp.NameSpace(strZip).CopyHere strPath & strfile
    oApp.NameSpace(CStr(strZip)).CopyHere strPath & strfile
    DoEvents

    Set oApp = Nothing
    Set zipFil = Nothing
    'uncomment next line to delete file after zipped
    'If objFSO.FileExists(strZip) Then objFSO.DeleteFile (fil) '- only delete source file is zip file is present
End Sub

Open in new window

0
TerenceHewett
Asked:
TerenceHewett
  • 7
  • 3
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
test this



Private Sub Command0_Click()
Set objFSO = CreateObject("Scripting.FileSystemObject") 'Create instance of FSO

Compress ("C:\FolderA")
Set objFSO = Nothing
End Sub


Sub Compress(fil)
    Dim strPath, strExt, strfile As String

    strPath = Left(fil, InStrRev(fil, "\"))     'Acquire Path
'    strfile = Mid(fil, InStrRev(fil, "\") + 1)     'Acquire filename
'    strExt = Mid(strfile, InStrRev(strfile, ".") + 1, 3)     'Acquire extension
    Dim strZip As String
    strZip = mid(fil,instr(fil,"\")+1)

    Dim zipFil As Object

    Set zipFil = objFSO.CreateTextFile(strZip)
    zipFil.WriteLine Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, 0)
    zipFil.Close

    Dim oApp As Object


    Set oApp = CreateObject("Shell.Application")
    'oApp.NameSpace(strZip).CopyHere strPath & strfile
    'oApp.NameSpace(CStr(strZip)).CopyHere strPath & strfile
    oApp.NameSpace(CStr(strZip)).CopyHere oApp.NameSpace(fil &"\").items
    DoEvents
    On Error Resume Next
    Do Until oApp.NameSpace(ZipFileName).Items.Count = _
                oApp.NameSpace(fil & "\").Items.Count
    DoEvents
    Loop
    On Error GoTo 0


    Set oApp = Nothing
    Set zipFil = Nothing
    'uncomment next line to delete file after zipped
    'If objFSO.FileExists(strZip) Then objFSO.DeleteFile (fil) '- only delete source file is zip file is present
End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
oops,

change this
strZip = mid(fil,instr(fil,"\")+1)

with
strZip = mid(fil,instr(fil,"\")+1) & ".zip"
0
 
TerenceHewettAuthor Commented:
Hi Cap, and thanks for your help.

I get a runtime error 424 object required on the following line:

Set zipFil = objFSO.CreateTextFile(strZip)

As a note, when I hover over strZip, it does give me "FolderA.zip", but zipFil is empty when hovered over.

Any ideas why?

Your help is really appreciated as usual.

Regards
Terry
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Rey Obrero (Capricorn1)Commented:
change these lines

    Set zipFil = objFSO.CreateTextFile(strZip)
    zipFil.WriteLine Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, 0)
    zipFil.Close

with


        Open strZip For Output As #1
        Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
        Close #1
0
 
TerenceHewettAuthor Commented:
Hi Cap,

I get run time error 91 : object variable or with block variable not set on the following line:

oApp.NameSpace(CStr(strZip)).CopyHere oApp.NameSpace(fil & "\").Items

When I hover over "fil" I get the following exact text:

 "c:\folderA"

and when I hover over "strZip" I get the following exact text:

"FolderA.zip"  (does not contain the path)

Your continued help would be so appreciated on this. I am pulling my hair out.

Regards
Terry
0
 
TerenceHewettAuthor Commented:
Just to mention, in case it is relevant to the problem, I am using Win Vista Business.  

Regards,

Terry
0
 
RgGray3Commented:
The routine I created for my own use...  takes SourceFolder and TargetFileName

The only bit of code that would need modification is that I use another function to return the users home desktop path and place the zip on the users desktop.

as stated in the comments it requires references to Shell32.dll and Windows Scripting




Sub CreateZipFromFolder(strSourceFolder As String, strZipFileName As String)
' --------------------------------------------------------------------
' (C) 2005-2011, Richard Gray, III Software     All rights reserved.
' --------------------------------------------------------------------
' Parameters: strSourceFolder - The Full Path Folder Name
'             strZipFileName  - The FileName for the ZipFile (No Path
' -----------------------------------------------------------------------------------------
' Comments  : Tested and placed in library 2/6/2011
'               Requires references to Shell32.dll and Windows Scripting Host
'               IWshRuntimeLibrary  C:\WINDOWS\system32\wshom.ocx
'               Shell32             C:\WINDOWS\system32\SHELL32.dll
' -----------------------------------------------------------------------------------------
On Error GoTo PROC_ERR

Dim ShellApp            As Shell
Dim fso                 As New FileSystemObject
Dim strFpTargetFileName As String

Const cstrExt           As String = ".zip"

    Set ShellApp = CreateObject("Shell.Application")
        
    '---------------------------------------
    'Confirm that we have a Zip extension
    '---------------------------------------
    If Right(strZipFileName, 4) <> cstrExt Then
        strZipFileName = strZipFileName & cstrExt
    End If
    
    '---------------------------------------
    'Create the Full Path Target FileName
    '---------------------------------------
    strFpTargetFileName = GetHomeDesktop() & "\" & strZipFileName
    
    '---------------------------------------
    'Create the Zip Header
    '---------------------------------------
    fso.CreateTextFile(strFpTargetFileName, True).WriteLine "PK" & Chr(5) & Chr(6) & String(18, 0)

    '---------------------------------------
    'Copy the files into the zip file
    '---------------------------------------
    ShellApp.NameSpace(strFpTargetFileName).CopyHere (strSourceFolder)

    '-------------------------------------------------------------------------------
    'Tell the user the good news
    '-------------------------------------------------------------------------------
    DoCmd.Beep
    gstrMbMessage = "Compressed File Name" & vbCrLf & _
                    strZipFileName & vbCrLf & _
                    "Created on your Desktop"
    gintMbStyle = vbInformation + vbOKOnly + vbDefaultButton1
    gstrMbTitle = "Compressed File Created"

PROC_EXIT:
    MsgBox gstrMbMessage, gintMbStyle, gstrMbTitle
    Set ShellApp = Nothing
    Set fso = Nothing

    Exit Sub

PROC_ERR:
    gstrMbMessage = Err.Number & ": " & Err.Description
    gstrMbTitle = "Zip File Creation Error"
    gintMbStyle = vbInformation + vbOKOnly + vbDefaultButton1
    Resume PROC_EXIT

End Sub

Open in new window

0
 
RgGray3Commented:
BTW:  I also use 3 global vars for my message boxes so you would need to modify the msgbox calls to suit your own style of coding
0
 
TerenceHewettAuthor Commented:
Thanks RgGray3,

I have set the references as instructed and I have debugged and no errors are reported.  Although - I get run time error 91 : object variable or with block variable not set when I run the code.

Any ideas to what i am doing wrong?

Regards
Terry
0
 
TerenceHewettAuthor Commented:
The 91 error in the post above highlights the following line of code:

    '---------------------------------------
    'Copy the files into the zip file
    '---------------------------------------
    ShellApp.NameSpace(strFpTargetFileName).CopyHere (strSourceFolder)

Not sure what I am doing wrong.
0
 
TerenceHewettAuthor Commented:
No matter - all fixed.  I was being thick as usual.

Regards

Terry
0
 
TerenceHewettAuthor Commented:
Sorry - manners....thank you very much for all the help.

Regards

Terry
0
 
RgGray3Commented:
Well...   It's funny because I was going nutz just a week ago with the same problem...
Found a series of routines that either were not complete or would not compile...

When I finally distilled what I had found I was suprised to realize how little code was actually involved...

Glad to help and share...

Rich
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now