File Zip with VBA

I have this code which has been used several times but when I copied it to a new Access database, it fails with "Compile Error:  Sub or Function Not Defined" at 'Pause (1.5)'.  Can you please advise why this might be happening?

Public Function ZipOut()
Dim oApp As Object
 
Set oApp = CreateObject("Shell.Application")
    oApp.NameSpace([filepath] & [zipname].zip").CopyHere [filepath] & [Inputfilename].txt"
    Pause (1.5) 'for one and a half second pause
Set oApp1 = Nothing

End Function
LVL 1
CFMIFinancial Systems AnalystAsked:
Who is Participating?
 
Nick67Commented:
This is a better way to accomplish your wait and your zipping.
Because 1.5 second is arbitrary

Sub ZipAFolder(PathAndFolderToZip As String, ZipPathAndName As String)
    Dim oApp As Object
    
    'Create empty Zip File
    NewZip (ZipPathAndName)
    Dim ToBeZipped
    Dim ZipTo
    ToBeZipped = PathAndFolderToZip
    ZipTo = ZipPathAndName

    Set oApp = CreateObject("Shell.Application")
    'Copy the files to the compressed folder
    oApp.Namespace(ZipTo).CopyHere oApp.Namespace(ToBeZipped).Items

    'Keep script waiting until Compressing is done
    On Error Resume Next
    Do Until oApp.Namespace(ZipTo).Items.count = oApp.Namespace(ToBeZipped).Items.count
        Sleep 250 'uses the API sleep function to wait in 1/4 second intervals until the zip completes
    Loop
    On Error GoTo 0
    Set oApp = Nothing
End Sub

Open in new window

0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
You probably will need to also import the VBA code for the Pause() routine
0
 
Nick67Commented:
Pause (1.5) 'for one and a half second pause

That isn't a native VBA function
Someone has composed a function called Pause(SomeName as Single) in the old database
within it, they either have a WinAPI sleep()  (good) or DoEvents (not so good) rotine composed

So here, put this in a code module

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Sub Pause(interval as Single)
if isNumeric(interval) = false then
exit sub
end if
dim millis as Long
millis = CLng(1000* interval)
Sleep millis
end sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
CFMIFinancial Systems AnalystAuthor Commented:
Okay.  Did that a the " "Compile Error" ceased.  Now for same code, it returns a "Run-time error '91':  Object variable or With block variable not set. . .
0
 
CFMIFinancial Systems AnalystAuthor Commented:
Nick67 - Do I need Public Sub Pause(interval as Single) if I use your sleep 250 ?
0
 
Nick67Commented:
No, if you implement Sleep by putting it in a code module (it cannot go in a form or report module!) you can call it simply by

Sleep 1000 'one second pause
Sleep 250 'quarter second pause
Sleep 50 '50 millisecond pause
Sleep 60000 'one minute pause

Now for same code, it returns a "Run-time error '91':  Object variable or With block variable not set. . .
What code tosses that error?
0
 
CFMIFinancial Systems AnalystAuthor Commented:
Oops.  My bad.  Please disregard the "Run-time error '91': and I need to recheck what I've got so far.  I'll get back with you.  Thanks.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
CFMI,  I did fix the original issue in your question. You now have a new issue.
0
 
CFMIFinancial Systems AnalystAuthor Commented:
Thank you.
0
 
Nick67Commented:
The Shell zipping is good, but not entirely bulletproof.
Not in my experience anyway.
You can call PowerShell from VBA and you can zip files using PowerShell
http://mcpmag.com/articles/2014/09/29/file-frontier-part-6.aspx

I haven't changed my production code, but its worth think about if you're starting out new
0
 
CFMIFinancial Systems AnalystAuthor Commented:
I agree and am still having issues but I'll ask a new question for them.  Thank you.  (I cannot use an outside application resource due to company policy so I'm working with what I have.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
CFMI,

Sorry. I thought you wanted to fix the compiler error. (Which I did)  I did not understand that you wanted a new solution to zipping.
0
 
CFMIFinancial Systems AnalystAuthor Commented:
Boyd - No worries.  I'm easy when it comes to getting help.  I'll do what's asked.  with respect to I think your message to 'import', I'm kind of a VBA novice and didn't know there was a specific 'VBA pause code' which I needed to add.
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.

All Courses

From novice to tech pro — start learning today.