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
moriniaAdvanced Analytics AnalystAsked:
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.

Randy PooleCommented:
How are you loading the table?
0
moriniaAdvanced Analytics AnalystAuthor Commented:
I am reading an Oracle table using a select statement.
0
Martin LissOlder than dirtCommented:
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
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

MacroShadowCommented:
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
0
Martin LissOlder than dirtCommented:
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
0
Randy PooleCommented:
You may be able to run the SQL Statement in async mode like this:

Private WithEvents cn As ADODB.Connection
private sdate as Date
private rdate as Date
Public Sub RunQuery()
   Dim connectionString As String: connectionString ="your connection information"
   
   Set cnA = New ADODB.Connection
   cn.connectionString = connectionString
   cn.Open
   cn.Execute "your SQL Statement", adExecuteNoRecords, adAsyncExecute
   sdate=Now
   TimerEvent
end Sub

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

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

Open in new window

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
moriniaAdvanced Analytics AnalystAuthor Commented:
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
0
Randy PooleCommented:
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
0
Randy PooleCommented:
where is it giving the error?
0
moriniaAdvanced Analytics AnalystAuthor Commented:
See attached file.
Error.PNG
0
Randy PooleCommented:
Sorry, try this:
Application.OnTime EarliestTime:=rDate, Procedure:="TimerEvent", Schedule:=True
0
moriniaAdvanced Analytics AnalystAuthor Commented:
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
0
Randy PooleCommented:
Guess you could just display it like:
"Loading Data " & Now
0
Martin LissOlder than dirtCommented:
You could do

"Loading Data " & Format(Now,"HH:MM")
0
Martin LissOlder than dirtCommented:
That gives military time. If you don't want that then do

Format(Now, "hh:mm am/pm")
0
moriniaAdvanced Analytics AnalystAuthor Commented:
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
0
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 Excel

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.