W
wfc
Hi,
I need help in writing a Macro that can format data that has been
brought in to the spreadsheet using winwedge pro and a DDE.
I have only been working with macros for the last week and so far have
just only been able to get the input with the DDE working.
I am using Excel 2000.
To give beackground information i have a micro controller feeding out
data on to a serial port and then i use win wedge to put the data in to
excel using the DDE in a macro.
For each test the data puts out 3 sets of data. 1 displacement array
and 2 acceleration arrays(from 2 seperate accelerometers).
what happens it that for every change in displacement the
microcontroller records the displacement a reading from accerometer 1
and a reading from accelerometer 2.
once the test is finished either by reaching 2500 displacement values
or being timed out the micro controller will output the data on to the
serial port.
it will come out in to excel like this
each new line in a new column,
100
98
98
65
23
end
600
500
600
655
400
end
600
528
655
455
322
end
now this test is just a short one of 5 displacement values, then 5
acceleration values from accelerometer 1 and 5 acceleration values form
accelerometer 2. the first displacement value corresponds to the first
acceleration value from each accel array and so-forward.
Now the issue i have is that the size of these tests are always
different and can be anywhere up to 2500 displacement values long
(making the whole data column 7500 values long.
what i need to make is a macro that after the DDE has placed the data
in the spreadsheet it will find the first "end" and cut from that cell
down to the bottom of the data series and paste it in the next column
and then delete the "end" to allign the data points. Then it will need
to perform the operation again in this second column to make a 3rd
column.
once it has performed that i need to the top of the 4th column to wait
for a next set of data to do it all again.
so the end result would have the data looking like this
100 600 600
98 500 528
98 600 655
65 655 455
23 400 322
these 3 data arrays will always be the same length as each other in the
single test, but the test sizes can change.
Eg. test 1 above has 5 points, but test 2 may have 2500 points.
i have tried recording a macro to do this however i am unsure what
steps to take to make it always cut from the first "end" down while
taking in to account different test sizes as the macro only cuts and
pastes from referencing cell positions.
thanks
Warren
I need help in writing a Macro that can format data that has been
brought in to the spreadsheet using winwedge pro and a DDE.
I have only been working with macros for the last week and so far have
just only been able to get the input with the DDE working.
I am using Excel 2000.
To give beackground information i have a micro controller feeding out
data on to a serial port and then i use win wedge to put the data in to
excel using the DDE in a macro.
For each test the data puts out 3 sets of data. 1 displacement array
and 2 acceleration arrays(from 2 seperate accelerometers).
what happens it that for every change in displacement the
microcontroller records the displacement a reading from accerometer 1
and a reading from accelerometer 2.
once the test is finished either by reaching 2500 displacement values
or being timed out the micro controller will output the data on to the
serial port.
it will come out in to excel like this
each new line in a new column,
100
98
98
65
23
end
600
500
600
655
400
end
600
528
655
455
322
end
now this test is just a short one of 5 displacement values, then 5
acceleration values from accelerometer 1 and 5 acceleration values form
accelerometer 2. the first displacement value corresponds to the first
acceleration value from each accel array and so-forward.
Now the issue i have is that the size of these tests are always
different and can be anywhere up to 2500 displacement values long
(making the whole data column 7500 values long.
what i need to make is a macro that after the DDE has placed the data
in the spreadsheet it will find the first "end" and cut from that cell
down to the bottom of the data series and paste it in the next column
and then delete the "end" to allign the data points. Then it will need
to perform the operation again in this second column to make a 3rd
column.
once it has performed that i need to the top of the 4th column to wait
for a next set of data to do it all again.
so the end result would have the data looking like this
100 600 600
98 500 528
98 600 655
65 655 455
23 400 322
these 3 data arrays will always be the same length as each other in the
single test, but the test sizes can change.
Eg. test 1 above has 5 points, but test 2 may have 2500 points.
i have tried recording a macro to do this however i am unsure what
steps to take to make it always cut from the first "end" down while
taking in to account different test sizes as the macro only cuts and
pastes from referencing cell positions.
thanks
Warren