To retrieve a single cell's value for all Excel files in a folder

Dear all,

I need to retrieve a single cell value for all Excel files located in a folder called 'Customers'. For each Excel file there, the task is to capture the single value of cell Sheet1!I41.

The end output are just two columns: column 1 is the Excel file name, and column 2 is the cell value Shee1!I41.

Can you please give me a macro to accomplish this task? Thank you.

Try

Please Login or Register  to view this content.

Hi Crooza, thanks for your help

I tried your code and it seemed that there is some problem:

1) There is a message popping up all the time: "This workbok contains links to other data sources". So I need to constantly click the 'Update' button.

2) After that, the macro stopped with an error message: "Run-time error '13': Type mismatch".

I added the statement On Error Resume Next. But the macro ran for a while and terminated unexpectedly and the file closed.

Can you please have a look? Thanks

Hi jindon, thanks for your code. I ran it and no output shown in the Excel file

Originally Posted by alice2011

Hi jindon, thanks for your code. I ran it and no output shown in the Excel file

Did you select the folder from the folder picker?

It should work and it is not opening files at all.

Hi jindon. Thanks again. Yes, it is actually now working. Maybe because the files are located in a network drive. This may cause a problem.

But now I copied the source files to local C drive. It worked perfectly. Really very cool. I will again aim to learn from your code. Very helpful...

Hi Crooza, thanks for your quick reply. Yes, your code works! Not sure why it did not work in the first instance, maybe because the files are in a network drive? Anyway, thank god. Your code works nicely if I copy the source files across to the local C drive. Thank you. Really helpful