Script Help - Compare Cell Value against File Name for Multiple Files into one XL

Hi,

I have a problem which i was hoping someone can help with.

I have 2500 MS Excel Files, they are in 11 different folders. (But could be in the same folder).

I need a way to look inside every excel file and return 2 values so i can compare them.

My output would be a single spreadsheet, with 2500 rows (excluding header)

Column A = File Name (without extension)
Column B = Cell E4
Column C = True/False to check if Col A=B.


I do have a formula that will return the filename with no extension into the cell. But I have no idea of how to do this quickly for all files into a single comparison worksheet.

=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1,SEARCH(".",CELL("filename",A1))-1-SEARCH("[",CELL("filename",A1)))

Please help!!

Thanks in advance

Originally Posted by Mumps1

First of all, it may take some time for a macro to run through 2500 files. It would help if the files were all in one folder. What is the full path to the folder containing the files and what is the extension of the files (xlsx, xlsm)? What is the name of the sheet containing the data you want to examine? Is the sheet name the same in all 2500 files? What do you mean by "Column C = True/False to check if Col A=B"? Do you want to compare all of column A to column B? Please clarify.

Hi there Mumps,

Thanks for your interest and checking if you can help.

OK, so.

I will put all in 1 folder and make all files xls

Folder name = D:\Site Data\

Inside site data there are 2500 xls files. The 2500 xls files are the same except for the content. So there is a single worksheet named Input.

I want to create one excel file where it has looked at each of the 2500 files and took 2 pieces of data from each and compared them to check if they are equal.

I want to compare the filename of the xls file, with cell e3 for the same xls file. And validate of it is equal or not.

My result should be. 1 xls file which has 2500 rows. In column A should be the filename without extension, Column B should be equal to cell e3 of the same source file,and column c should contain true/false.

I hope I explained properly.
Many thanks.

Originally Posted by Mumps1

You want to compare the filename to cell E3 in the same file. Does the filename in cell E3 include the extension?

No it does not.

File name = Site ID.xlsx
Cell E3 = Site ID

The site ID is 5 digits in every case. 1 Letter and 4 numbers.

In the Summary worksheet, i get this as an output.

Is the script not picking up the contents of Cell E4 in the source files? Each of the source files consists of 2 worksheets. 1 Active sheet, named Data Input and 1x hidden sheet named Drop Downs.

I have tried running it with the drop downs sheet deleted, but it did not help.



D0010 FALSE
H0470 FALSE
H0502 FALSE
K0638 FALSE
M0009 FALSE
M1158 FALSE
M1340 FALSE
N0006 FALSE
Q0450 FALSE
Q0452 FALSE
U0004 FALSE
Z1234 FALSE

1) Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html

2) Do all workbooks in the folder have specific sheet name?

[QUOTE=jindon;5184559]1)

1) Thanks, will do.

2) Yes, Data Input. (There is 1 additional sheet in all which is hidden and named Drop Downs

I'm waiting for you to edit your 2 posts...

Originally Posted by jindon

I'm waiting for you to edit your 2 posts...

Sure thanks.

Still working on this is anyone can help.
Really no idea how to fix the problem with not bringing the cell E4 into the summary.

OK, thanks for the code tags.

Untested.

Please Login or Register  to view this content.

Hey Guys,

Mr Mumps1 - Your code working fine. It required 2 additional line. Really not sure how or why, but i just played with it and got it working.
One reading the cell e4 and putting it into the target. Would share the line, but the forum thinks i am posting links when i try to share the code.


Jindon, out of interest, im really trying to make your code work also, because during this process i found VBA incredibly useful. I analysed and googled all your code, and i still cant work it out. What i do know, is that this code does the same thing but much faster. I believe due to the Array?

Your code is taking cell C4 rather than cell E4 and putting it into Column B.
Can you advise which part of the code actually tells the script to look into C4 and not e4?

Thanks in advance, you guys saved me probably 3 days :-)

My bad E4... should be r4c5, row4, col5 in R1C1 notation.

Please Login or Register  to view this content.

Should be very fast, because it runs without opening each files.

Originally Posted by jindon

My bad E4... should be r4c5, row4, col5 in R1C1 notation.

Please Login or Register  to view this content.

Should be very fast, because it runs without opening each files.

Makes perfect sense. Thanks for the explanation.

(Googles: "How can a vba script extract data from a cell without opening it")

Dear Mumps1 & Jindon,

Thank you both for your help with this script.

I can conform both methods worked perfectly.

One interesting thing to note, is that Method 1 Mumps1 took 28 minutes to execute.
Method 2 from Jindon that utilises ExecuteExcel4Macro, took 30 seconds to execute.

I have marked this as Solved.
Thank you again.