CFMI
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.Applic ation")
oApp.NameSpace([filepath] & [zipname].zip").CopyHere [filepath] & [Inputfilename].txt"
Pause (1.5) 'for one and a half second pause
Set oApp1 = Nothing
End Function
Public Function ZipOut()
Dim oApp As Object
Set oApp = CreateObject("Shell.Applic
oApp.NameSpace([filepath] & [zipname].zip").CopyHere [filepath] & [Inputfilename].txt"
Pause (1.5) 'for one and a half second pause
Set oApp1 = Nothing
End Function
You probably will need to also import the VBA code for the Pause() routine
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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. . .
ASKER
Nick67 - Do I need Public Sub Pause(interval as Single) if I use your sleep 250 ?
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?
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?
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.
CFMI, I did fix the original issue in your question. You now have a new issue.
ASKER
Thank you.
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
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
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.
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.
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.
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.