Need Some Guidance for New Spread Sheet Design

[Ufdah]

Here's the big picture. I need to Design an Excel spreadsheet that wil
import a csv text file that is 575 colums wide (fields) and 720 row
(records) that I can then graph.

Now the details. The purpose is to be able to graph historical dat
from a Data Logger that collects data every two minutes (720 rows
day) for 575 different pieces of equipment, and I will only need t
graph a couple things at a time. I started doing this through Acces
and imported the file into a 2-D Array in VBA since there is the 25
column (field) limit, then just pulled the data I needed out of th
array and put it into a table which I could use to chart. But all th
access guys say that Excel is much better at charting and more flexibl
so I decided to see what was possible on this side. I was thinking o
approaching it simlarly in Excel but instead of a temp charting table
would use a Sheet. I really am doing no calculations and the only use
input will be to select what pieces of equipment they want to graph an
the time frame for which to graph. I currently don't have plans for th
ability to do more than one day as that would be more than one fil
import but it would be a nice feature.

I guess what I'm looking for is a little direction from those of yo
who are more seasoned in this to hopefully make this development
little less painful. Thanks

 

[Guest]

Off the top of my head, I think I would approach the problem by breaking up
the 575 machines into groups, and importing only portions of the total to
several different Excel Sheets..........(to overcome the 256 column limit),
then make a "Main menu" sheet to do the selecting and/or data
entry............this could eventually all be under macro/Query control and
could import the data for any given date you select. It would of course
require dicipline on naming the data storage files, and on their format.

hth
Vaya con Dios,
Chuck, CABGx3

 

[Jef Gorbach]

Unfortunately with 575 fields, your not going to be able to import directly
into Excel b/c of the 255 column max unless u figure how to break each row
across multiple sheets AND keep them linked together

However your 2d array should work mostly as-is since Access/Excel use
similar variations of VBA to save only those input fields you want to an
Excel worksheet which would then be used to build the graphs.

There is also a 256-worksheet limit so your going to need two files to
track/graph all 575 peices of equipment, so you might consider saving the
input data in Access and having Excel's vba query it as needed for the
desired fields? Doing so might not only byass the 255 col/sheet limits but
also let you keep more than 1-day for each equipment.