Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access VBA - programmatically add files to an existing zip

Posted on 2011-03-17
5
Medium Priority
?
2,473 Views
Last Modified: 2016-09-13
hello,

Does anyone know how to programmatically add files to an exisiting zip file? I'm using code to create the zip file with a spreadsheet in it. However, I need to add PDFs to that zip file. The reason it needs to be done with VBA code and not dragging/dropping into the zip is because the code will run overnight when no one is around.

btw, i'm trying to use 'FileCopy strDirFrom, strDirTo', to copy the PDF to the zip file but I keep getting Run-time error '76': Path not found. This is confusing me because I know the paths are right. Are you not able to programmatically copy to a compressed zip file?

any ideas will be greatly appreciated.

thanks!
aebea
0
Comment
Question by:Anthony Berenguel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 2000 total points
ID: 35158461
I posted this in your previous Q....must have been while you were requesting deletion.  Anyway, this code creates the zip file first and then adds files to it.  Simply comment out the part where the zip file is created.
OM Gang


Public Function AddFilesToZip()
On Error GoTo Err_AddFilesToZip

        'Declarations
    Dim objFSO As Object, objZip As Object, objShell As Object
    Dim objFolder As Object, objFile As Object
    Dim sngStart As Single
    Dim strPath As String, strZip As String
   
    strPath = "C:\test\"   'Path to read files from
    strZip = "C:\test2\omgang.zip"  'Output zip file
   
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objZip = objFSO.CreateTextFile(strZip)
    objZip.WriteLine Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, 0)
    objZip.Close
 
    Set objShell = CreateObject("Shell.Application")
    Set objFolder = objFSO.GetFolder(strPath)
 
        'loop through files - adding them to the zip
    For Each objFile In objFolder.Files
       
        objShell.NameSpace("" & strZip).CopyHere objFile.PATH
       
        sngStart = Timer
        Do While Timer < sngStart + 2
            DoEvents
        Loop

    Next

Exit_AddFilesToZip:
        'destroy object variables
    Set objFile = Nothing
    Set objFolder = Nothing
    Set objShell = Nothing
    Set objZip = Nothing
    Set objFSO = Nothing
    Exit Function

Err_AddFilesToZip:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Function AddFilesToZip of Module Module7"
    Resume Exit_AddFilesToZip
   
End Function
0
 
LVL 10

Author Closing Comment

by:Anthony Berenguel
ID: 35159538
thanks! This bit of code really helped me out!!
0
 
LVL 10

Author Comment

by:Anthony Berenguel
ID: 35159600
omgang, thanks again for your help. Btw, i'm not using this bit of code because I'm not sure what it does. Can you explain it? Thanks!

 
       sngStart = Timer
        Do While Timer < sngStart + 2
            DoEvents
        Loop

Open in new window

aebea
0
 
LVL 28

Expert Comment

by:omgang
ID: 35159685
It's to provide a bit of extra time to allow the file to be added to the zip file.  The code I provided adds every file in the specified directory to the zip archive.  When dealing with relatively large files we need to allow for a bit of time for the file to be processed.  The loop pauses processing of the current procedure for the specified interval (2 seconds) before continuing.  The other processes continue to execute during this pause, e.g. the Shell CopyHere command that's adding the file to the zip.

If you were dealing with very large files you may need to increase the interval.  If you are only dealing with very small files, or only one file at a time instead of mulitiple files, you may not require the pause at all.
OM Gang
0
 

Expert Comment

by:Justin Barry
ID: 41796593
OM Gang,

I am zipping some very large .txt files (1.4gb).  Is there a way to modify this code that uses the Sleep method, instead of adding to the interval?  IE: sngstart + 500?

If there isn't a way to use the Sleep method, do you see any issues with the process stalling when adding such a large interval?

Thanks!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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