Datalogging with Excel

  • Thread starter Rogelio Diaz Flores
  • Start date
R

Thread Starter

Rogelio Diaz Flores

Hello, I'm using the "ontime" VBA function to query data (about 100 registers, read through the serial port with an activeX driver) from a PLC every 10
seconds and store it in consecutive worksheet rows. I limit the sheet size to 1000 rows, retaining only the last 1000 samples. Trouble is, the computer gradually begins slowing down and the system clock slows down, so that after 10-12 hours the clock is 1 hour behind and the computer is so slow that it needs rebooting.

My PC is not the latest - a compaq laptop, 266MHz PII, 64 MB RAM - but for this application (maintaining a 100 column by 1000 row data table) I would guess it
should be enough. Any ideas or comments will be gratefully received.

(BTW, I'm using Excel 2000)

Rogelio
 
Rogelio;

By estimating the size of the Excel 2K file you're generating, then 64 meg RAM isn't anywhere near enough to handle a file that large. I imagine your Hard Drive is doing a lot of "thrashing", trying to exchange data between the swap file and RAM.

I don't think your 266 MHZ PII is the problem. Contact Compaq and see how much additional RAM you can still install in your laptop and fill it up to the limit.

Increased RAM will undoubtedly solve your problem.

To test this theory,,,, try running your app on another computer with LOTS
of RAM.

Bet it runs much better.

Mark Hill
Microsoft Associate Expert
[email protected]
 
Rogelio Diaz Flores:
> > Trouble is, the computer gradually begins slowing down and the system
> > clock slows down, so that after 10-12 hours the clock is 1 hour behind
> > and the computer is so slow that it needs rebooting.

Mark Hill:
> By estimating the size of the Excel 2K file you're generating, then 64
> meg RAM isn't anywhere near enough to handle a file that large.

If the problem is that the computer *gradually* begins slowing down, it
looks more like a memory leak in Excel or the VB script.

First: look for features that might be keeping the "deleted" rows around,
like ``Unlimited Undo''. Turn them off [1].

Second, look on the Internet/KB for any information about "memory leak" in
Excel, VBA or the driver, and how to work around it.

If neither of those help, there's not much you can do about it. If the
process lets you have a 5-minute break once or twice a day, you can keep
going as you are (perhaps adding RAM to extend the time between breaks).

Otherwise, you'll have to rewrite the script, eliminating whichever part is
leaking (whether that's Excel, VBA or the driver).

Another possibility would be to have two computers running, so that the
other one can do the logging while the first reboots. Then you can reboot
hourly.

> Increased RAM will undoubtedly solve your problem.

No, increased RAM will only help if you can reboot the computer regularly,
or at least close Excel and re-open it.

If you need it to keep running, you need to solve the original problem.

> To test this theory,,,, try running your app on another computer with
> LOTS of RAM.

To test my theory, run the app on another computer with somewhat more RAM;
if I'm right, it'll run longer but still bog down eventually.

> Mark Hill
> Microsoft Associate Expert


Jiri

[1] Obviously, you have to trust MS here that the option actually does turn
the Undo off rather than just disabling the menu option.
--
Jiri Baum <[email protected]>
http://www.csse.monash.edu.au/~jiribvisit the MAT LinuxPLC project at http://mat.sf.net
 
F

Friedrich Haase

Moin all,

I am not an Excel guru. So I cannot help. Here just an idea which might be
worth to check.

During usual usage Excel like most other programs collects data for the undo
feature. If it does something similar during automated execution it would
collect tremendous amount of data and eventually swaps it to disk. This
would cause trouble as described by Regelio and others.

Regards
Friedrich Haase
 
J

Johan Bengtsson

I don't know if this have anything to do with your problem but anyway....

We are sometimes using a serial to profibus converter and the driver associated with it to comunicate with external hardware, this also slows down the clock (in our case it seems to be a constant approxiamtely 12% slowdown regardless
of computer used) Since we deal with educational software and hardware we have simply ignored this (even if I find it really annoying) In out case we are not using anything in office at all and it behaves the same on different windows
plattforms.

I know the driver we use to be poorly written (it doesn't work at all if the computer have more than one processor unless the "extra" are turned off).

You can monitor the use of processor power and check if that gradually increases. I think the processor power needed for this should be really low.
If you are using windows NT or 2000 you can monitor processor use per application (and even thread) and see what uses what.

I does completely fail to see any reason the system clock lags behind, both in your and in my case and is interested in anything you find out about that...

/Johan Bengtsson

----------------------------------------
P&L, Innovation in training
Box 252, S-281 23 H{ssleholm SWEDEN
Tel: +46 451 49 460, Fax: +46 451 89 833
E-mail: [email protected]
Internet: http://www.pol.se/
----------------------------------------
 
Hi,
I am having problem to communicate with excel using serial com port to communicate with analog signal. Please advise me on this matter. Thank you.
 
Hi !

Try to write an VB app (really simple)
and use ODBC to put it into an acess database instead.

Be free to e-mail for a sample prog.

Best Regards Mathias Lindgren
 
P
I am sure that you are using Excel in a way that it was neither designed for nor tested for.

You are reading and storing 100 values every 10 seconds, 36,000 values/hour, 360,000 values in the 10 hours to the point that Excel slows to an unacceptable slowness.

I think that you need to find another solution that is designed for this requirement. As you need only the last 10,000 values- you could store them in memory in a custom way and extract them to Excel when needed.

Peter Clout
Vista Control Systems, Inc.
176 Central Park Square
Los Alamos, NM 87544-4031
(505) 662-2484
FAX (505) 662-3956
[email protected]
http://www.vista-control.com
 
Top