morinia
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:
........................St ill 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
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:
........................St
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
How are you loading the table?
ASKER
I am reading an Oracle table using a select statement.
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
Application.Cursor = xlWait
and at the end
Application.Cursor = xlDefault
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:
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
Progress-Bar-V1.01.xlsm
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
UserForm1.Show vbModeless
and this at the end
Unload UserForm1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.DisplayStatusB ar = 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
public sub TimerEvent()
Application.DisplayStatusB
Application.StatusBar = "Loading Data " & DateDiff("s",Now,sDate)
rdate=Now+TimeValue("00:00
Application.OnTime EarliestTime:=rDate, Procedure:=TimerEvent, Schedule:=True
end Sub
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
where is it giving the error?
ASKER
See attached file.
Error.PNG
Error.PNG
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
Guess you could just display it like:
"Loading Data " & Now
"Loading Data " & Now
You could do
"Loading Data " & Format(Now,"HH:MM")
"Loading Data " & Format(Now,"HH:MM")
That gives military time. If you don't want that then do
Format(Now, "hh:mm am/pm")
Format(Now, "hh:mm am/pm")
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.DisplayStatusB ar = ""
Application.OnTime EarliestTime:=rDate, Procedure:="TimerEvent, Schedule":=False
Use pRecordset to populate what you need
end sub
Timer.txt
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.DisplayStatusB
Application.OnTime EarliestTime:=rDate, Procedure:="TimerEvent, Schedule":=False
Use pRecordset to populate what you need
end sub
Timer.txt