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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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 MVPDesigner and DeveloperCommented:
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 MVPDesigner and DeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.