asked on

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

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

You probably will need to also import the VBA code for the Pause() routine

Nick67

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

ASKER CERTIFIED SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CFMI

ASKER

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. . .

CFMI

ASKER

Nick67 - Do I need Public Sub Pause(interval as Single) if I use your sleep 250 ?

Nick67

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?

CFMI

ASKER

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.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

CFMI, I did fix the original issue in your question. You now have a new issue.

CFMI

ASKER

Thank you.

Nick67

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

CFMI

ASKER

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.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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.

CFMI

ASKER

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.