Copying averages from multiple csv files to a summary xls using VBA

[Dirk-Jan]

Hi all,

As a cell biology researcher I am not too familiar with VBA and programming in general, so please bear in mind I'm a beginner. After spending quite some time trying to find an online solution to my problem on various message boards, I decided to join up and share my challenge with you guys.

The problem is as follows: I have a cell stimulation setup that produces a small csv file every ~150 seconds (while experiments with this setup can last for days...). This file contains 6 columns, 4 of which describe measured values in different channels. From each file I'd like to extract the average signal (rows 30-80) minus the average background (rows 90-109) for each of the four channels (columns C-F). Signal minus background *1000/0.5 gives me the electrical resistance of my stimulated cells.

Making use of different bits of code found online and typed by yours truly, I compiled the following macro:

Please Login or Register  to view this content.

What I want it to do is list the filenames of the csv files in column A, and resistances for channels 1-4 in columns B-E next to the name of the file they're derived from. The code as given above, however, only results in a 'runtime error 1004: application-defined or object-defined error'. All suggestions and improvements are welcome, since manual processing of these files is not exactly an option

Thanks in advance for your help!

Dirk-Jan

PS: Since I wasn't allowed to upload csv files I converted a sample file to xls before uploading it to give you an idea of the data involved.

[shg]

Welcome to the forum.

Try this. If it breaks, say what line.

Please Login or Register  to view this content.

You can upload any kind of file you wish if you zip it.

[Dirk-Jan]

Hi shg and thanks for your reply. I tried the code and it also gives me a 1004 runtime error. Using F8 I could see the Cells(iRow, 1).Value = sFile line as being the last one turning yellow before the error message is given.

When I replaced the variable iRow in that line by nRow the macro did run without errors. Also the list of filenames in column A is generated. The other columns remain empty though and no averages are copied, so I'm not quite there yet.

Oh and thanks for the comment on uploading files. I'll keep it in mind.

[shg]

When I replaced the variable iRow in that line by nRow the macro did run without errors.

My bad.

I'm not quite there yet.

So comment out the screenupdating line and step through the code.

[Dirk-Jan]

So comment out the screenupdating line and step through the code.

I'm sorry but could you please rephrase that? As mentioned above I'm a beginner and I do not quite understand what you advise me to do

[shg]

Open the VBE, open the module that contains the code, and delete the lines that include ScreenUpdating from the macro.

Then press F8 to step through the code and watch it work.

Strongly suggest you take the time to read http://www.cpearson.com/excel/Debug.htm

[Dirk-Jan]

Thanks for the link shg, it was very helpful in getting to know the debug functionality. After a holiday and some additional reading I gave it another try and got the code to work. The problem was not assigning clearly what object was to be worked on by the program. In the end I did modify what kind of info was copied: instead of subtracting the average of line 90-109 from that of line 30-80, I now sort the columns first and just take the top 2-11 as signal and the bottom 10 as background since not all data files are the same. That I could do by recording a macro while opening a data file and performing the desired steps.

I just wanted to share my solution with you, as well as ask an additional question. Since not all data files are usable (some don't contain any peaks, others are shifted, hence my different approach in getting the average), I'd like to add something to the macro. As it opens each csv file in column A and calculates the averages in these files before copying to my master Data_Extractor file, I'd like the program to save the files in a different location (e.g. "Used") if the criterion of "at least one of the calculated values is over 0.05" is met. In that way, I can discard the data files that don't contain usable data and only continue with the "Used" files. The line I wrote to do that is just not working yet. That being said, the code as given below runs without errors and produces the desired result.

Please Login or Register  to view this content.

Thanks in advance for your help.

Dirk-Jan

[Dirk-Jan]

Ah I just posted the version without the line that should send good files to the "Used" folder. The line I wrote is located at the very end of the macro:

Please Login or Register  to view this content.

As mentioned, this doesn't work yet. I suspect the If-statement is correct, but the saving isn't.

[Dirk-Jan]

Ah I just posted the version without the line that should send good files to the "Used" folder. The line I wrote is located at the very end of the macro:

Please Login or Register  to view this content.

As mentioned, this doesn't work yet. I suspect the If-statement is correct, but the saving isn't.