Triggering an event for a progress bar

Hi

I have some VB code I am trying to adapt to VBA in order to zip and unzip files with the AbleZip ActiveX. I am struggling with how to activate a progress bar to show the user progress on the event.

The code below zips a file and the Sub Function is the event to display progress, but I can't seem to put the two together. Can anyone help please?

Many thanks

Public Function ZipDataFile()
Dim AbleZip1 As Object
   
    Dim ResultCode As abeError
    ' All properties keep their default values Except the two below
    Forms!Preferences!AbaleZip1.FilesToProcess = "C:\Program Files\CMITS\My File.mdb"
    Forms!Preferences!AbaleZip1.ZipFilename = "C:\Program Files\CMITS\FTP\MyZip.zip"
    ' Start zipping
    ResultCode = Forms!Preferences!AbaleZip1.Zip
    ' Check the return value.
    If ResultCode <> aerSuccess Then
        MsgBox "Unsuccessful. Error # " + Str(nErr) + " occurred. " + _
        "Description: " + Forms!Preferences!AbaleZip1.GetErrorDescription(avtError, ResultCode)
    Else
        MsgBox "File(s) successfully zipped."
    End If

End Function

>>>Here is the File Status event that I am not sure how to incorporate into the above to display progress.

Private Sub xZip_FileStatus(ByVal sFilename As String, ByVal lSize As Long, _
                            ByVal lCompressedSize As Long, ByVal lBytesProcessed As Long, _
                            ByVal nBytesPercent As Integer, ByVal nCompressionRatio As Integer, _
                            ByVal bFileCompleted As Boolean)
   
    ProgressBar1 = nBytesPercent
End Sub
martmacAsked:
Who is Participating?
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.

Leigh PurvisDatabase DeveloperCommented:
(Not having used it personally...)
It looks like it's just a function that you poll routinely that returns a percentage figure of the job's completion.
(Guess it runs synchronously).

So if you had a timer on your form set to maybe 100 (tenth of a second) and that Timer event calls that sub to update your progressbar control ProgressBar1 (another ActiveX no doubt).
0
jjafferrCommented:


look for "SysCmd Method" in Access help

jaffer
0
harfangCommented:
Hello,

Is this event generated by the ActiveX? If  that is the case, you need to create a reference to the ActiveX object library (from VB: Tools / References, and [Browse]). Then, on the form itself (or in another class module, but that is a little more complex), do this:

Dim WithEvents xZip As AbaleZip   ' <-- use correct library and object type here

Note that this is a wild guess. I don't know anything about AbaleZip, it's just that your code fragment looks so much like a cut-n-paste sample event handler...

Perhaps before you try that, open the xZip's properties (that is the name of the control, right?) and see if there is a "FileStatus" event. Or go to the form's module, select xZip in the combo on the left and see if you have the event on the right...
Come to think of it, the name of your control might be AbaleZip1 and not xZip afterall.

In any case, don't try to do this from some other module. Do it from the form's module.

Hope this helps
(°v°)
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.

Leigh PurvisDatabase DeveloperCommented:
It does indeed look like an event procedure (I'm too busy plucking parsley!  Although FileStatus is an odd name for an event - StatusChange or NewStatus... but that is an odd one.  Hey... Programmers huh? ;-)

The only relevant argument that it passes for you in this instance being the percentage.

Is all you're missing the progress bar?
You likely already have the zip control and library in place - and the event will likely be raised internally.

You could look to jaffer's suggestion, use an ActiveX progress bar (or roll your own using rectangles).
0
martmacAuthor Commented:
Thanks guys

It appears it is an event per example code below. What I am not sure how to do is trigger the event in VBA

I hope this makes sense.

Thanks

'The FileStatus Event is triggered during the processing of each file. It provides
'progress information about the status of each file.
'------------------------------------------------------------------------------------
Private Sub (I have changed this to reflect the name >>AbaleZip1_FileStatus(ByVal sFilename As String, ByVal lSize As Long, _
                            ByVal lCompressedSize As Long, ByVal lBytesProcessed As Long, _
                            ByVal nBytesPercent As Integer, ByVal nCompressionRatio As Integer, _
                            ByVal bFileCompleted As Boolean)
    PrgressBar1 = nBytesPercent
End Sub

'=========================================================================

I am now not calling the ActiveX from the form

'=========================================================================

Public Function ZipDataFile()
Dim AbleZip1 As Object

Set AbaleZip1 = New AbaleZip
   
    Dim ResultCode As abeError
    ' All properties keep their default values Except the two below
    AbaleZip1.FilesToProcess = "C:\Program Files\CMITS\My File.mdb"
    AbaleZip1.ZipFilename = "C:\Program Files\CMITS\FTP\MyZip.zip"
    ' Start zipping
    ResultCode = AbaleZip1.Zip
    ' Check the return value.
    If ResultCode <> aerSuccess Then
        MsgBox "Unsuccessful. Error # " + Str(nErr) + " occurred. " + _
        "Description: " + AbaleZip1.GetErrorDescription(avtError, ResultCode)
    Else
        MsgBox "File(s) successfully zipped."
    End If

End Function
0
Leigh PurvisDatabase DeveloperCommented:
If the control itself doesn't expose this event - then exactly as Markus (Harfang) described - you can WithEvents an object variable of the appropriate type.

You don't trigger the event - you react to its firing with the code you place in its procedure.

WithEvents objects can only exist within a class module - hence making your form the most sensible location.
(You declare it in the declaration section at the top of the module - and... well I'll not waffle on any more - as the ActiveX control itself may expose the Event adequately).
0
martmacAuthor Commented:
OK, If I place the Activex control in a form, I can get the progress bar to work without any WithEvents statement.

How difficult is it to do this via a Public Function inside a separate Module, how would I use the WithEvents to feed the progress bar? I would prefer to do this rather than use a form if possible.

Many thanks
0
Leigh PurvisDatabase DeveloperCommented:
Only class modules can contain events.

You could have a standard module contain a function or sub and that be called from the event in a class module - but not the event itself - by very definition.
0
harfangCommented:
Let's work with an example. This uses ADODB and a table called Customers. First create a class module (from VB, "Insert" menu) and paste this:

------------------------------------------------------------------------------------
Option Explicit

' the following allow event catching for an object:
Public WithEvents rec As ADODB.Recordset

' flag to communicate with caller
Public Busy As Boolean

Private Sub Class_Terminate()

' called when the object is set to Nothing or goes out of scope
' (used for clean-up operations)
    Set rec = Nothing
   
End Sub

Private Sub rec_FetchComplete( _
    ByVal pError As ADODB.Error, _
    adStatus As ADODB.EventStatusEnum, _
    ByVal pRecordset As ADODB.Recordset)
   
' called "once in a while" during record fetching...
    Debug.Print "fetch complete"
    Me.Busy = False

End Sub

Private Sub rec_FetchProgress( _
    ByVal Progress As Long, _
    ByVal MaxProgress As Long, _
    adStatus As ADODB.EventStatusEnum, _
    ByVal pRecordset As ADODB.Recordset)

' called (if time permits) when all records have been fetched...
    Debug.Print "... progress: " & Progress & " (" & MaxProgress & ")"

End Sub
------------------------------------------------------------------------------------

Save and call it "claRecordsetEvents". To use it, you will need a object of that type, which you can do in a normal module. Add the following to an existing module or in a new _normal_ module:

------------------------------------------------------------------------------------
Sub TestEvents()

    Dim MyEvents As New claRecordsetEvents
    Dim recTest As New ADODB.Recordset
   
    ' set MyEvents to catch recTest's events:
    Set MyEvents.rec = recTest
    MyEvents.Busy = True
    Debug.Print "Opening Customers"
   
    ' open an asynchronous recordset
    ' (fetching records in the background)
    recTest.CursorLocation = adUseClient
    recTest.Open "Customers", _
        CurrentProject.Connection, _
        adOpenStatic, _
        adLockReadOnly, _
        adAsyncFetchNonBlocking
   
    ' use ample DoEvents so that many events get fired
    Do While recTest.State And adStateFetching Or MyEvents.Busy
        DoEvents
    Loop
    Debug.Print "We have " & recTest.RecordCount & " records"

End Sub
------------------------------------------------------------------------------------

Now type "TestEvents" in the immediate pane and [Enter]. Study the above and you will see how this can be adapted to your case. The main difference being that you will use AbaleZip events and not ADODB.Recordset events...

Gook Luck!
(°v°)
0
harfangCommented:
Hmm, I switched the comments for the events. But you caught that already, didn't you? ;)
(°v°)
0
Leigh PurvisDatabase DeveloperCommented:
Hmm when you asked about a "Public Function inside a separate Module"...
Was it in fact a separate _class_ module you were interested in?

If you aren't wanting to use a control as such - and do this entirely through an object variable then absolutely - fair enough.
However - if you do have a control (and indeed need a form to show the progress bar on) then might you just as well use it?
If you're envisaging using this in several locations - and hence wanting some core function in the centre I'd have thought having the one "zip and progress" dialog form that is called from wherever in your application would be the way to go.
0
harfangCommented:
I agree, Leigh. A form seems the most logical choice. The argument about the progress bar being likely on a form is especially strong...

As a matter of fact, the same technique can be used with a form, including:

    Set MyEvents = New Form_fmodZipProgress
    MyEvents.Visible = True

That way, both the ActiveX events and the pop-up progress bar form would be bundled into one object...
Gotta watch those memory leaks, though ;)

(°v°)
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
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.

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.