Automatically delete rows in several Excel-files according to user choice ("batch mode")

I have a couple of excel files in one folder carrying time sequence data. Every file has only one sheet and it carries the names of the columns in the first row. Data starts from row 2 and can be several thousands of rows = data points. Task is to delete sequences of consecutive rows and keep others - according to user specifications which rows to keep and which to delete.

Task

Part I. Get information from user (either via dialog/ User Form or anything you like) how to proceed with the data:

a) user specifies input folder (that's where the excel files are)

b) user specifies output folder (that's where the processed files go)

c) user specifies sequences = row numbers which indicate the "end" of each time sequence (the user should be able to specify up to 15 row numbers, which mark the borders between sequences (eg. 2 (beginning of data), 15 (=end of first sequence), 2.345 (= end of second sequence), 2.453 (= end of third sequence), 5.347 (end of 4th sequence), 10.453 (end of 5th sequence), etc. -> this means sequence 1 goes from row 2 to 14, sequence 2 goes from row 15 to 2.344, sequence 3 goes from 2.345 to 5.346, sequence 4 goes from 5.347 to 10.452, etc.

d) specify what to do with each defined sequence of rows (k = keep; d = delete)

Part II Procedure

For every file in specified folder

a1) Look for column named "Bessemer" (or other, specified in advance by user)

a2) Delete (crop) all rows beginning from row 2 until first value starting with "Video" (or other specified in advance by user) is found in column "Bessemer"

a3) Look for last value starting with "Video" found in column "Bessemer" and delete all rows starting from this row to last row

b) Insert sequence numbers in column "Bessemer" as specified by user (see Part I.)

c) If sequence is specified as "k" by user (see Part I.) keep all rows of this sequence. If sequence is specified as "d" by user (see part I.) delete all rows of this sequence

d) save processed excel file in target folder

Part III Putting Excel Files together

Merge all the columns of the different excel-files in the target folder together in one new excel file and save this file.

Article Writing 代笔 报告写作

项目ID： #10143505

关于项目