forming Ticker-Tape in Status Bar after running web Query

  • Thread starter Thread starter gr8guy
  • Start date Start date
G

gr8guy

Hi,

I am trying to retrieve data from an external webpage which refreshes
automatically every 15 secs. It displays the list of agent ID's logged in to
our system at a particular time.

The webpage shows a data in table format similar to this:

Agent ID TimeOnCall someothercolumns


1) Is it possible to SORT the extracted data on a worksheet OR a locally
available webpage file on a network drive,

2) Show the AgentID's along with LAST TimeOnCall for that agent who logged
out, (scrolling or displayed) in the Status Bar of a Worksheet or webpage,
by which I would come to know that this agent has just logged out & is not
working? So that i can approach the agent & ask him to login again.

Any Help would be appreciated!

Thanking you in advance.

Eijaz
 
If you put the data in the worksheet, it can be sorted.

If the list is a list of agents logged in, then you would need a list of all
agents and have to make a comparison against this list to determine who
isn't logged in.

If you want to build a test string of this write code to animate it by
removing a character from one end and adding a character to other, then you
can put this information in the status bar. Excel provides no particular
support for the animation and it would be time intensive - distracting from
you 15 sec refresh of the data in the web page.

I can't say what you can do with a web page.
 
Tom Ogilvy said:
If you put the data in the worksheet, it can be sorted.

If the list is a list of agents logged in, then you would need a list of all
agents and have to make a comparison against this list to determine who
isn't logged in.

If you want to build a test string of this write code to animate it by
removing a character from one end and adding a character to other, then you
can put this information in the status bar. Excel provides no particular
support for the animation and it would be time intensive - distracting from
you 15 sec refresh of the data in the web page.

I can't say what you can do with a web page.
Click to expand...

This is a suggestion. If you know where the logout info is stored in
your system you should be able to (as long as your system is windows
conpatable) write a program to interface with it to look for the
required information and post it to your sreadsheet. But do this with
care.

Hope this gives you some ideals.

Charles
 
Hi,

Both of you are not getting what i am trying to do!

I am extracting the tabular data to a worksheet from a webpage which shows
the Agents logged in to our outbound/inbound dailer system. this webpage
refreshes itself in 15 secs. Ok.

Now, after i extract data on a worksheet, i want to also refresh the
worksheet every 15 secs in sync with the webpage. Ok.

Now, the extracted data could be sorted and then matched with a helper
column in the worksheet which would contain the full list of agent ID's in
our Dept.

But, if suppose after 15 secs, one of the agent ID is missing from the
extracted list, the Match would fail and his LAST logged in time and ID name
(when his ID name appeared last time, 15 secs prior to refresh) should
reflect in the worksheet status bar as a scrolling text or just show up
there for some time. The need for scrolling text is bcos if there are more
than one agents logged out, their names would reflect one after the other in
the scrolling text.

I know that there would be variables to define & some use of the Timer &
TimeOut event.

Something like this in the status bar:
eijazs - 12:55:03 am......zaki - 12:57:04 am ...etc.
This way i can keep a track of whose logged in and who's logged out, getting
the agent strength on the floor sitting at one place. No need for me to go
and individually check approx 200 agents on the floor.

Regards,

Eijaz
 
You'll have 4 lists.

1. The webpage list
2. A current copy of the webpage list
3. A copy of the webpage list as it was 15 seconds ago with timestamp
4. A list of all Agents

Copy list 2 to list 3
Copy list 1 to list 2
For all agents in list 2, write status of "logged in" against agent in list
4
For all agents in list 3 minus agents in list 2, write timestamp against
agent in list 4
Refresh ticker string using list 4 agents where agent not "logged in"

You'll need another timer type procedure running in the background which
manipulates the Application.StatusBar text using the ticker string.
 
Hi Rob,

Was browsing your website when saw ur msg come in to my post.

The problem is, I donot know how to write the scrolling TickerTape program
for the status bar & how to go about doing that.

Rgds,

Eijaz
 
Here's a ticker I just whipped together... It actually looks better than I
expected.


Public TickerString As String
Private Const TickerSpeed = 3

Sub StartTicker()
DisplayTicker
TickerString = "abcdefghijklmnopqrstuvwxyz"
End Sub

Sub StopTicker()
On Error Resume Next
Application.OnTime Now() + TimeValue("00:00:01"), "DisplayTicker", ,
False
Application.StatusBar = False
End Sub

Sub DisplayTicker()
Static i As Long
Dim strTemp As String

If i = 0 Then i = 1
strTemp = Mid(TickerString, i) & Mid(TickerString, 1, i - 1)
i = i + TickerSpeed
If i > Len(strTemp) Then i = 1
Application.StatusBar = strTemp
Application.OnTime Now() + TimeValue("00:00:01"), "DisplayTicker"
End Sub
 
Thanks Rob,

But where do i put it?

do i insert it in a new module or Can i put it in the Thisworkbook Code?

Rgds,

Eijaz
 
Hi,

I just now found a Javascript code for a scrolling TickerTape. Is It
possible to convert this to the VBA code?

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">

<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
<meta name="GENERATOR" content="Microsoft FrontPage 2.0">
<title>JAVA-SCROLL </title>
</head>

<body bgcolor="#F652FF"
onload="timerONE=window.setTimeout('scrollit_r2l(100)',100);"
<a href="jv_main.html" target="display">

<script language="JavaScript">
<!-- Begin code

function scrollit_r2l(seed)
{
var m1 = "Mumma...";
var m2 = "Aireen...";
var m3 = "Eijaz...";
var m4 = "Zaki...";

var msg=m1+m2+m3+m4;

var out = " ";

var c = 1;

if (seed > 100) {
seed--;
var cmd="scrollit_r2l(" + seed + ")";
timerTwo=window.setTimeout(cmd,100);
}
else if (seed <= 100 && seed > 0) {
for (c=0 ; c < seed ; c++) {
out+=" ";
}
out+=msg;
seed--;
var cmd="scrollit_r2l(" + seed + ")";
window.status=out;
timerTwo=window.setTimeout(cmd,100);
}
else if (seed <= 0) {
if (-seed < msg.length) {
out+=msg.substring(-seed,msg.length);
seed--;
var cmd="scrollit_r2l(" + seed + ")";
window.status=out;
timerTwo=window.setTimeout(cmd,100);
}
else {
window.status=" ";
timerTwo=window.setTimeout("scrollit_r2l(100)",75);
}
}
}
// -- End code -->
</script>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p><em><strong>@@@@@The Members of my Family@@@@@.</strong></em></p>
</body>
</html>

Rgds,

Eijaz
 
Oh by the way, I forgot to inform you guys that whenever i am trying to get
data using the webquery on an excel page, I get a dialog box with the cancel
button showing & the following message showing:

"Excel has generated errors & will close. A log file has been created."
Then excel application automatically closes. What could be the problem?

Rgds,

Eijaz
 
It's very similar to what I supplied. What differences do you want to see?

Yes, it can be converted to VBA.
 
Hi Rob,

A very good morning to you.

The difference is evident. the one which you supplied is located in the
bottom left side of status bar and does not scroll across status bar but
appears a scrambled name. and the one which i provided (javascript), scrolls
ACROSS the status bar, probably because of the spaces in front and after
text. The need for a scrolling text message is :
1) a scrolling marquee cannot be created in a worksheet itself, as it is an
array of cells (like a scrolling marquee in a webpage).
2) i need it like that because there would be many agents getting logged
out, so their AgentId & time on which they logged out would reflect in the
scrolling text msg (from left to right, or right to left) across the status
bar. So I would be able to see (for some time) the no of agents logged out
in the current duration. the ticker-tape would hold variables for suppose 3
agents & run, till further agents get logged out & then the variables would
automatically be updated with the new AgentIDs & run along status bar. So
probably, if no agents logged out the last agents IDs would show in the
scrolling ticker-tape.

e.g. scrolling like this:
....eijazs - 1:05 am ....... hzaki - 1:10 am.....saireen -
1:12am........rocka - 1:15 am........jeank - 1:25 am........eijazs -
1:05am....

I dont know if its a great or stupid idea, but there should be a workaround
of creating a code which would find the agents logged out & the
corresponding time they logged out from the refreshing webpage.

I have never dealt with web-queries before & finding it difficult as to how
to create a code or a work-around which would show the agents logged out.
And right now, i am getting a stupid error when i access the webpage: "excel
has generated errors and will close the application. a log file has been
created."
Do you know why this is happening?

Is there any way we can workout a codable solution for my problem?


Best Regards,

Eijaz
 
I don't know what you did differently, but the version at my end scrolls and
isn't scrambled.
TickerString = "...eijazs - 1:05 am ....... hzaki - 1:10
am.....saireen - 1:12am........rocka - 1:15 am........jeank - 1:25
am........eijazs - 1:05am...."
 
Try the one i sent you!

Paste it into a notepad & save as scroll.html. You will see the difference.
I am not able to convert it to VBA code.

Rgds,

Eijaz
 
I did! I didn't see a lot of difference between the one I wrote and the
Javascript version.
Specifically, what is different that's upsetting you? I'm at a loss.
 
Hi Rob

Thanks for your reply

See, what i mean is i get the agentId's in Suppose Col A, and their logged in time in Col B
I want to collect the AgentID's & Logged-in-Time in variables(or an array for each Column A & B) & then
pass them to the TickerTape function which will display them scrolling one - after - the other across the length of the Statusbar, either from left to right direction or from right to left direction
similar to a scrolling Marquee on a webpage which travels horizontally across the webpage

after the 15 sec refresh, new data will be loaded by webQuery, in the columns A & B on the worksheet & that would be taken in the variables(or array) and then passed to TickerTape function which will show the AgentID's & their logout time, one-after-the-other scrolling across the length of the statusbar

Could you please convert the Javascript code which i submitted to VBA for me inorder to get the same effect which it shows on the webpage

And what about the Excel error (excel generated errors......will close......a log file has been created). do you why its happening? is it because of version of IE used(IE 4.0) or because of the particular webpage i am accessing, which is on our company intranet

Best Regards

Eija


----- Rob van Gelder wrote: ----

I did! I didn't see a lot of difference between the one I wrote and th
Javascript version
Specifically, what is different that's upsetting you? I'm at a loss
 
Back
Top