asked on

Sending notifications when processing in VB - Excel 2010

Experts,

I have an Excel spreadsheet reading an Oracle table. The table is extremely large. Is there a way to send notifcation to user every 5 minutes saying:

........................Still Processing Time: 11:47

This way it shows the program is still not running and not hanging. I have seen in the past, where a program is running and Excel is displaying (Not Responding)

If possible I would like to send the notification without the use having to reply as in Message Box commands

Randy Poole

How are you loading the table?

morinia

ASKER

I am reading an Oracle table using a select statement.

Martin Liss

I don't believe you'll be able to do that. The only thing I think you'll be able to do is at the start of the process add this

Application.Cursor = xlWait

and at the end

Application.Cursor = xlDefault

Joe Howard

There are two options, you can use a userform to create a progress bar, but that requires advanced programming skills, the other option is sending the message to the status bar (a lot easier but not as noticeable.

Attached you will find a sample progress bar project in Excel. Read the article by the creator.

You can send a message to the status bar using the following code:

Application.DisplayStatusBar = True
Application.StatusBar = "Hello"
Application.StatusBar = "Goodbye"
Application.DisplayStatusBar = False

Open in new window

Progress-Bar-V1.01.xlsm

Martin Liss

You could also display a modeless userform that says "Working..." with this at the start

UserForm1.Show vbModeless

and this at the end

Unload UserForm1

ASKER CERTIFIED SOLUTION

Randy Poole

membership

This solution is only available to members.

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

Start Free Trial

morinia

ASKER

Are you calling TimerEvent() within itself. I am note sue what this piece is doing. It is giving me an error

public sub TimerEvent()
      Application.DisplayStatusBar = True
      Application.StatusBar = "Loading Data " & DateDiff("s",Now,sDate)
      rdate=Now+TimeValue("00:00:30")
      Application.OnTime EarliestTime:=rDate, Procedure:=TimerEvent, Schedule:=True
end Sub

Randy Poole

This will immediately set the status bar without waiting 30 seconds as well as set the OnTimer to return to this sub in another 30 seconds

Randy Poole

where is it giving the error?

morinia

ASKER

See attached file.
Error.PNG

SOLUTION

Randy Poole

membership

This solution is only available to members.

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

Start Free Trial

morinia

ASKER

Randym

That did fix the problem. Is there a way to format the Date/Time so it is easier understood? Now it is displaying
Loading Data - 3614684130

Randy Poole

Guess you could just display it like:
"Loading Data " & Now

Martin Liss

You could do

"Loading Data " & Format(Now,"HH:MM")

Martin Liss

That gives military time. If you don't want that then do

Format(Now, "hh:mm am/pm")

morinia

ASKER

Randy,

I am having a problem as to where I put this code. I have attached my module, that reads the table and writes the results. Is it possible for you to incorporate this code in my module?

private Sub cn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
      Application.DisplayStatusBar = ""
      Application.OnTime EarliestTime:=rDate, Procedure:="TimerEvent, Schedule":=False

      Use pRecordset to populate what you need
end sub
Timer.txt