+'&title='+encodeURIComponent(document.title)+'&overview='+encodeURIComponent(document.title)+'&urllanguage=en&urlaffiliate=32003&encoding=UTF-8&urlpicture=http://www.viadeo.com/v_img14/shareit/viadeoflower.gif')
So I made a new version that keeps the swarm gratuitously swarming for no other reason than to make it look like they are busy doing something. I've never liked any of the timer options for VBA so I often use Javascript for this, like I have in this updated example. I've tested the workbook in Excel 2002 and 2007 and it seems to work ok. Let me know if you have any problems.
Here's the update with the fully active swarm!
P.S. A minute ago I just about fell out of my chair. I ran the program and the swarm looked like they were on steroids, or maybe they were killer bees! It took me a second to realize that I had the Stereokinetic illusion running at the same time in the same instance of Excel but not in the foreground. Both programs make extensive use of calculation events and the two running at the same time was almost scary. I thought the swarm was about to take their revenge on me...
If you liked this article, please share it!
Daniel,
Great work as usual....
Can your approach be used in the below mentioned scenario.
Assume you have an Excel file with several queries(around 25) built on an Access DB
You want to refresh them.
Currently you would have to refresh them one by one... Is it possible to use a swarm to refresh the queries.... asynchronously
Hi, Sam.
Yes, this could work. Instead of using the MSXML2 object from the VBScript agents, you would use ADO instead.
If your Access DB is local, I'm not sure how much of a speed gain will result, since the one physical machine will be doing all the work.
In the web scraping example, the big bottleneck is the internet connection, which is thousands of times slower than a local disk. Waiting synchronously for results, one at a time is not efficient. The swarm is a way around synchronous access to the bottleneck.
It may be that you could see an improvement in the Access DB using the swarm, but I don't think it will be as dramatic.
I'd love to hear the outcome if you try it!
Regards,
Daniel Ferry
excelhero.com
I am going to give it shot....I will e-mail you in case I get stuck with the Vb script part...
I a real life scenario the DB will be on a network folder or even a web folder.... there might be a improvement but not very dramatic.
On my old computer:
345 sec for swarm
485 sec for MSXML
David,
I suspect that old computer is a little shy on RAM. One downside of this technique is that it requires about 10 MB of RAM for each agent. So, in your situation, you may get better performance by reducing the number of agents in the swarm.
Regards,
Daniel Ferry
excelhero.com
Swarm 81 Seconds
MSXML2 453 Seconds
from Australia
8GB RAM i7 930
Win 7 Office 10
The red line went above 2 for about 3-4 seconds
Welcome back, Hui!
Is that your post on Chandoo's site this morning? Impressive.
Thank you for the performance feedback of the swarm on your rig. If more people send in their metrics, I can compile a performance chart, so we can all learn what works best.
Regards,
Daniel Ferry
excelhero.com
Daniel
Yes, That is my post on PHD.
I haven't got back to looking at that code I sent you yet, It's on my list
Hui...
Hi Daniel!
I'm not very familiar to vbscpits and I'm still trying to to understand the code... Still a Newbie at VBA & codes! Anyway, never seen anything as fast as Swarms before.. pretty cool!
I'm having a much simpler problem, similar to the one you've solved at the post, that perharps you could give some highlights about it. I would like to have a particular table that is listed in a website imported to my sheet. Consider the site www.mukirana.com - a brasilian auction website. When I go to http://www.mukirana.com/auctions/440-camera-digital-10mp-12x-zoom-optico-lcd-%202-7-polegadas-fuji-leilao-mukirana, for example, there's a table called "ULTIMOS LANCES" which lists the latest bids related to this particular item. This is the table that I would like to import to my sheet. I tried to do it using the Excel Wiz (DATA - >IMPORT EXTERNAL DATA->IMPORT DATA), but the "flag" to import didnt appeared at that particular table...
Any help would be quite useful!
Thanks a lot in advance & keep on doin' those nice posts! Outstanding!
Hi, Luigi.
The problem with the built-in web query technology in Excel is that it can only work on HTML tables. The data you want is inside a DIV tag and not a TABLE.
One way to work around this limitation with the particular web page you are interested in, is to click the flag at the very top left of the page. It turns out that everything on this page is in one big table.
You would then need to set up a little table somewhere in your workbook that points to the relevant data in the sheet containing the web query. And by point I mean to use formulas, one for each cell of your table pointing at one cell in the web query results.
A little awkward, but it will get you the current pricing data in your little table each time you update the web query.
I've built extensive businesses reporting applications that use the built-in web query technology in Excel, but in those cases I controlled the web servers and was thus able to ensure that the report data was published in a TABLE tag.
If the site does not use HTML Table tags at all, then the web query technique will not work, and you would need to do something like my swarm, which gives you total control over what information is scraped.
Regards,
Daniel Ferry
excelhero.com
Hi Daniel
Thanks a lot for the explanations! It was quite useful! I wasn't aware about the built-in web query limitations. I mean, I was, but i didn't understand it.
Before commenting here, I had tried to import the entire table to my excel just as you said. The problem is that in the PC I was using, the IE version was pretty old, so I couldn't do it. After reading your tip I tried again (this time using my Notebook) and it went pretty well! Thanks again.
So long the macro is running fine. The basic idea is to get at each moment (in this case, 15secs) the 10 latest bids and paste it to a sheet. The macro then calculates how much each user has spent so far, as well as its Willingness To Pay, each user average BID and calculates for how long a certain user has been "bidding" in this auction. I thought this sort of information could give some highlights about the right "timing" to start bidding.
The only "problem" I`m having is that the macro is running low (around 7-10 secs). So, I'm tryin'to "clean" the code, to speed it up (swarms, for example). what about next steps? So far i think that tryin' to "import" the countdown ticker to excel would be cool. perharps, with it I could "design" a decision rule to another macro automatically starts bidding (say, whenever the ticker marks at 2secs)... but this is just a thought.. a different issue & certainly tons of vba!
Well, as you can see, your explanations have been quite useful!
Once again, congrats on your post & your explanations! Thanks a lot
Asi es! opino como tu!
I was searching for this type of info, my Gf is not recieving my phone so i need to impress her any how today. thanks for such info and i love google because they help us to get good info which i search always.
One of my school mate told me about your web site yesterday.Even i like your article very much.We are currently doing an project on this particluar topic.I believe we will get good marks because of your post thank you very much sir.
Well... Amazing!!!
Can you comment a little bit how you built the graph at the top of the sheet.
It looks like it has 5 different data sources, but I don't understand how you articulate them.
How are the bubbles created???
I also saw that you are using names that contains formulas, how do they get refreshed/recalculated?
It will take me nights to understand your code!!
What is the “asynchronous” agent?
How do the “bees” write their data back to the original Excel file?
How many years of practice to get to that level of excellence??
This is the best piece of code that I ever seen!!!
Thanks
simply
Fantastic work :O)
thx
Yes, fantastic work !
But could it be used to login to a secure web site using HTTPS asking for a ID_Name, a Password and a special key number (digipass : unique number for each session).
Thanks a lot in advance & keep on doin' those nice posts!
EDgn