Solved

Zipping a folder in Access 2003 using VBA

Posted on 2011-02-12
13
484 Views
Last Modified: 2012-05-11
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
Comment
Question by:TerenceHewett
  • 7
  • 3
  • 3
13 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
oops,

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

with
strZip = mid(fil,instr(fil,"\")+1) & ".zip"
0
 

Author Comment

by:TerenceHewett
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
 

Author Comment

by:TerenceHewett
Comment Utility
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
 

Author Comment

by:TerenceHewett
Comment Utility
Just to mention, in case it is relevant to the problem, I am using Win Vista Business.  

Regards,

Terry
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 11

Accepted Solution

by:
RgGray3 earned 500 total points
Comment Utility
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
 
LVL 11

Expert Comment

by:RgGray3
Comment Utility
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
 

Author Comment

by:TerenceHewett
Comment Utility
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
 

Author Comment

by:TerenceHewett
Comment Utility
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
 

Author Comment

by:TerenceHewett
Comment Utility
No matter - all fixed.  I was being thick as usual.

Regards

Terry
0
 

Author Comment

by:TerenceHewett
Comment Utility
Sorry - manners....thank you very much for all the help.

Regards

Terry
0
 
LVL 11

Expert Comment

by:RgGray3
Comment Utility
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now