?
Solved

Triggering an event for a progress bar

Posted on 2006-04-16
12
Medium Priority
?
472 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:martmac
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16464580
(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
 
LVL 27

Expert Comment

by:jjafferr
ID: 16464612


look for "SysCmd Method" in Access help

jaffer
0
 
LVL 58

Expert Comment

by:harfang
ID: 16464638
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16464680
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
 

Author Comment

by:martmac
ID: 16465398
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16465489
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
 

Author Comment

by:martmac
ID: 16465634
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16465768
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
 
LVL 58

Expert Comment

by:harfang
ID: 16466504
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
 
LVL 58

Expert Comment

by:harfang
ID: 16466527
Hmm, I switched the comments for the events. But you caught that already, didn't you? ;)
(°v°)
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 600 total points
ID: 16466686
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
 
LVL 58

Accepted Solution

by:
harfang earned 1400 total points
ID: 16466775
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month13 days, 16 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question