Searching Multiple Excel Sheets

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
Hi all,

I currently have a program which monitors vibration events and outputs a csv file (approx 6 meg) every 10 seconds. The monitor will usually run for between 30 and 40 minutes at a time.

The problem is, when I collect the data, I have to search each file individually and look for the one that has the highest vibration reading, which takes me a long time.

Basically I get vibration readings in columns B,C and D, from row 20 to row 200,019. Currently I open each sheet individually, and type =max(b20:B200019) in cell b19, drag that across to column d. I then enter =sum(B19:D19) in cell E19, and use the file with the the highest E19 value.

Is there a way I can run a macro that will look through every sheet in a specified folder, and tell me which worksheet has the highest values?

Any information would be appreciated.

Cheers

John
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Think outside the box please.

Why not - at the moment of doing the export to CSV - also take the required sum, and update the MAX and worksheet name in a certain location? This saves you an extra procedure, an expensive loop through the files in a folder, and so on. That location could be an Excel cell, a Defined name, etc.
 
Upvote 0
Thanks Wigi,

Unfortunately the csv file is produced from a monitoring unit with the manufacturers software installed. So as far as I can see I am unable to manipulate them/change the output
 
Upvote 0
Then you will have use the kind of Dir(...) loop that you find very often in code on the forum. Look for topics on "for every file in a folder" and you will quickly find code.
 
Upvote 0

Forum statistics

Threads
1,224,596
Messages
6,179,807
Members
452,944
Latest member
2558216095

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top