How to extract 2760 workbooks in a master workbook

Hi all,

I have above 2760 workbooks in one file, they are named differently however they all have the name "lime" associated with them. In each workbook there are values in multiple cells, these cells are:

1- Merged cells (C5:J5, C7:D7, A8:F8, C9:E9, A10:E10, C11:j11, f9:j9, h10:j10, C28:D28, E28:F28 and G28:H28)
2-Singular cells G15, G16, G17, G18, to G23,and E31

The question is will it be possible to extract the said cells for each workbook despite they are named differently but they have"dolo" incorporated with them?
and if so, will it be possible to list them in a master workbook in rows, where, the first row will the cells extracted from worksheet1 and row 2 will have data extracted from worksheet2 and so on.

Can anybody help

Regards,

No they are workbooks or Excel files, these files contain cells that I want to extract from each file and place in a master excel file. In the master file, I would like the cells to be placed in rows in top of each other; row1 for workbook or file #1, row2 for workbook2, and so on. this will enable me to conduct the statistical analysis I'm in seek for.

The file naming is that all the files begin with "Burnt lime report" next comes a date. yes all the files are located in one folder. I'm not familiar with VBA, but by any means (copy, cut, etc.) if I cant extract the data for each file to a master data that will be great. But I heard if a code was generated by VBA it can loop through these multiple files and extract data to the master data, this code is I'm after.

Here is a revision that allows you to choose which method to use to populate the cells. If you select No, then the files will be linked and it takes about 1/15 the time of opening each file.

WOW it worked, man your a life save, I really thank you deeply for your help. Allow me to try it more and more & I will update you on the finding.

Here is an example of data conversion based on your sample data file. I have deleted all the data except the data to be converted.

Original dates are in B, converted dates are in C.
Original tonnage is in D, converted sums are in E.
Original quantity is in I, converted sums are in J.

If you see a "???" it means that the value could not be parsed.

In the actual file, this would replace the values in the same column. In this example I added new columns so you could see how the data is converted and which values cause problems.

Many thanks for your prompt reply, I tried to copy the VBA associated with the date and pressed run on VB nothing is happening, I guess I got something wrong? However since I have compiled all the data in one folder and I have organized them in 6 worksheets, will it possible to create another extract to extract these data? Will this process the case easier? Or do you advice other easier method?

Here the columns are all located in one file with 6 worksheets as follows:

1. Column A, E, F, U & V: these columns are text, will it be possible to extract as text?

2. Column B, D, & H: these columns are date, will it be possible to extract as date and to show "???" when the date could not be processed, as your message suggests.

3. Column C & G : these columns are numbers, will it be possible to extract as a number, in these formats that you suggested :
68.64Tons
69.82+107.46Tons
130.58+47.48 T
129.36+88.14+42.88 T
150.6+117.44+76.66+50.04 Tons

Sometimes it’s written with text in front too (Tonnage: (68.64Tons or your above formats)), will it be possible to disregard the word tonnage too and process as you suggested too? Incase other formats to show "???", so I can manually adjust.

4. Column I, J, K, L, M, N, O, & P: these columns are numbers, to extract as numbers.

5. Column R, S, & T: these are numbers, to give one example they can be in the form
>50 mm = 1.47
Will it be possible to extract the number after "=" only? In this example to extract the number (1.47) as a number.

If extracting these sorted data works it will be of great help on my analysis.

Appreciated deeply your continues help and support.

The VBA is a demo that will work in that particular file. It won't work if you simply copy it into another file. If the results in that file are what you are looking for then I can install that code to the master file I sent you earlier.

I will review your latest post, but this question is growing and it will take some time. #1 is easy. The sample I just provided handles #2 and #3 as you described. I will have to look at the data for #4 and #5 to see what is required.

Hi, just checking see if this last update is helpful.

Hi, sorry for the belated reply as I was graphing these huge a mount of data to draw statistical inferences. The software is great and your help is and will always be appreciated. Thank you very much for your time and effort.