Transpose worksheet

[bcalb]

Hello everyone, I have the task of capturing data into a csv format file from another application whenever the data changes. I have for eg. 10 values that I am writing, at random intervals (meaning when there is a data change on anyone of them). I am successful in writing this data into a file along with the time. Now I want to transpose this data. My problem is that I want to be able to do this programatically, every hour generate a report (excel file with transposed data is printed). Since the number of rows is unknown it is a huge challenge. Please any pointers is appreciated. many thanks in advance. Please see the attachment on how I want to be able to display data finally.

[stevekirk]

HI,

try this and let me know.
this collects the data for a1 to d (to end of rows) and copies it in coloumn i

change it to suit your requirments

Please Login or Register  to view this content.

hope this helps
steve

[T-J]

What you could do is read the data in the CSV file into a recordset using ADO,
then use the GetRows method to transpose the records and fields into an array and then transfer the array to a worksheet.
One slight problem was 14:15:00 was appearing on the sheet as 2:15:00 PM. I got over this by formatting the time as a string.

Tested and working with the following TimeData.csv file:

Please Login or Register  to view this content.

Does your CSV file contain column headers (Time, Val1, etc)?

[bcalb]

Many thanks to Steve and TJ. Both your soltuions work well and suit my need. I am putting it all together and have a pretty happy end user. Thanks again.