Apologies in advance for the long post, but I think I need to explain things carefully so people
don't get confused!
A while ago I ventured onto these forums to seek some help regarding getting Excel to grab
data from a datalogger and place it into columns on a worksheet. Since then I've been working on electronics, actually
building the circuit and associated hardware, but now that the circuit works I've now moved back onto some programming to
improve the Excel sheet I have currently to deal with the data.
The original post on these forums can be found
Now as the worksheet used
to function it would collect the data every x seconds (set via VB, usually every 3 seconds, dependent on the button pressed),
update the top left of the worksheet with the current results every x seconds, and at the same time, copy and paste the
results into seperate columns (seperating each channel into a different column to make it easier to work with the data). This
would then repeat every x seconds, placing each new set of data for each channel into the row below the last one, giving me a
permanent record of all results taken so far. It then also placed a simple counter in one column (saying 1,2,3,4 etc down
each row) showing which number in the sequence the data collected was. I then had a set of cells with formulae in them to
work out a set of other variable from the original data obtained from the logger.
Having been playing around with the macro a bit recently, trying to make it do the things I want, I seem to have slightly
I have attached an example worksheet to show you what it currently looks like, and with it is a small
amount of sample data to show how it's working. Most of the macros are located in "Module 1", but some of the ones
referred to at the end are in Module 2.
If I haven't confused you too much already, let me try to explain what
I've been trying to make it do
-------- Simplify the 'interface' so that the whole thing can be triggered by one
button press (as other people will be using this, and I want it to be as simple as it can be), and also monitored in one
screen display (everything important can be seen without scrolling). To do this I made a new button (the "Start
Readings" button), and attempted to create a macro do to the following things when pressed:
1. Clear the
contents of the current data readings in the top left (range A4:C11)
2. Clear the contents of all cells in the range E17:W50000 [gets rid of all old data to make sure there is no mixing of
sets of results]
3. Grab the latest set of data from the data logger, and place it in the top left area.
4. Look down the newly collected data in the top left, and use the Channel Names as cell contents as column titles from E17
going right until all channel names have been used (so the number of columns used could vary depending on the number of
channels collected from the logger)
5. Then set the cells in row 17 to the right of the cells named in step 4 with a set of fixed titles (such as ALARM @150)
6. Then once all this has been set, call the macro which then collects the data from the logger every three seconds, and
places it in the newly named columns, moving down a row with every new set of results.
The point of this set of
steps is that when someone presses the Start button, it acts as a reset-all if you will, it clears out all old data, looks at
the logger and re-tunes itself to be ready for importing the new data, then starts collecting it.
I've encountered with these steps so far are:
1. works fine
2. I want the macro to look right down to the bottom row in Excel. This varies with different versions, and I have heard
there is some kind of bottom row, or last row function, but having searched the web for a bit failed to find it. This would
make sure it always clears out all the old data, by making it go right down to the bottom of the rows available. It's set at
50000 currently just because I can't find this function
3. works fine
4. Works, but I don't have the programming knowledge to make it more 'intelligent'; that is to make it only use the number
of columns that there are channels, so that there aren't unessecary empty columns. It currently just creates a few empty
columns that needn't be there. If lots more new channels are added, it also might not deal with them all as it may miss some
off the ends.
5. Works, but same as for step 4: if the macro knew how many columns it was using in 4, it could then move these named
columns right next to the data columns without gaps. There will be more columns in this area to come as the worksheet
6. This is where I seem to have really confused the macro! It now does quite a few strange things: places a batch of data at
the top of the worksheet, where it isn't needed. The first row to use needs to be row 18, just below the titles for each
column; it goes down a row every 3 seconds, but now ALL the rows update every three seconds with the new data - the old data
is no longer preserved, so it now just progressively reads more and more rows all giving the same, changing results.
--------Adapt and extend the macro to add the calculated cells (the ones in columns R-W in the example attached, although
there will be some more eventually) to be calculated and worked out as the macro goes along, rather than having all the
values pre-placed in the cells manually, as this gives a lot less flexibility, and also means that you can't perform the
"reset all" function I talked about above, as you lose the forumulae. There is also some conditional formatting
associated with the two "Alarm" columns, which can be seen if you look at the cell's formatting. Ideally, if any of
the cells in the two alarm columns flag up an alarm (and go red, displaying "Alarm"), then the large merged cell in
D9:E15 would then display some formatting change to show an alarm has been detected, and perhaps pop up a message box too.
--------Ideally the graph at the top would also auto update with the new data as it comes in, automatically adjusting
it's values when the sheet is reset etc. Currently I just placed the source data a long way down the sheet and it just
displays the rows with values in, but this isn't a perfect solution.
--------Change the Time Elapsed column, which
currently counts the number of the data record collected in each row, so that instead of simply counting the number (1,2,3
etc) it displays the current system time when each sample was taken. As it turns out (despite my old post) having the
'actual' time rather than just relative is actually more useful!
--------And if you want one final hurdle (which
I've started with help from someone at work), if you take a gander in VB from Module 1 to Module 2, there will you find some
code which is aimed at doing the following:
- as the data is recorded into the Excel file, it then also copies the results and places them in a comma-seperated text
file, writing them in real time, to act as backup of the data in case Excel crashes or someone does something silly and
deletes the data. The macro in this bit currently just counts up to a set time and displays a simple number count, also
showing a system time stamp, and places it in a text file located in the same directory as the Excel file is located, with
results comma seperated. It names the file currently "Testdd", with the dd being the current day of the month from
the system time. Ideally it would name them the full date (yymmdd hhmm) because once we start collecting hundreds, an easily
sortable file naming fomat will be invaluable!
Hmm, well this is indeed a long post with a lot of requests, and
I've no doubt quite confusing, so apologies for you lot trying to understand it, if you need any more clarification then
please ask and I'll try to explain it better!
As you can see I've got most of the areas started, and working to a
certain extent, but my VB powers fail before getting it to be as 'slick' as I would really like it.
Any inputs on
solving any of the issues mentioned is greatly appreciated!
**Edit: File was too large to attach, even zipped and
reduced as much as I could. Have uploaded it to a sharing site, can hopefully be found here:
**Edit again: in the uploaded version, the graph is missing as I deleted it
in an attempt to reduce the file size. The graph was set to display the two moving average columns of coil resistance change
against time elapsed, as a nice visual representation of what's going on.