?
Solved

Sending notifications when processing in VB  - Excel 2010

Posted on 2014-07-16
16
Medium Priority
?
202 Views
Last Modified: 2014-07-30
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
0
Comment
Question by:morinia
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40199712
How are you loading the table?
0
 

Author Comment

by:morinia
ID: 40199735
I am reading an Oracle table using a select statement.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40199768
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
Industry Leaders: 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 28

Expert Comment

by:MacroShadow
ID: 40199769
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
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40199783
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
 
LVL 21

Accepted Solution

by:
Randy Poole earned 1400 total points
ID: 40199943
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
 

Author Comment

by:morinia
ID: 40200059
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
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40200068
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
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40200070
where is it giving the error?
0
 

Author Comment

by:morinia
ID: 40200101
See attached file.
Error.PNG
0
 
LVL 21

Assisted Solution

by:Randy Poole
Randy Poole earned 1400 total points
ID: 40200107
Sorry, try this:
Application.OnTime EarliestTime:=rDate, Procedure:="TimerEvent", Schedule:=True
0
 

Author Comment

by:morinia
ID: 40200285
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
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40200303
Guess you could just display it like:
"Loading Data " & Now
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40200444
You could do

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

Expert Comment

by:Martin Liss
ID: 40200456
That gives military time. If you don't want that then do

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

Author Comment

by:morinia
ID: 40201929
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

621 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