feth data from cells from all the files in a folder

[Mehul Jaganiya]

Hey guys

I have seen a various VBA codes which do a part of what I am trying to achieve, but I couldn't find anything that helps me to do exactly what I want.

I have got a folder with around 500 excel files (all with a same template) (apart from excel files there are some other subfolders too in this folder). The address of that folder on my computer is \\STM-FS2\shared documents\shared documents\JOB COSTINGS. Now I want to fetch data from cells C4 and O17 from a worksheet named Hours from each file in that folder.

Please help me with a code that do this.

Thanks

[Leith Ross]

Hello Mehul Jaganiya,

Where will the output of the cells go?

[Mehul Jaganiya]

I have another excel file named Jobs on hand 27.10.14. In this file there is a worksheet named AllHours. The output can be populated in column A and B of this worksheet.

Thanks

[Leith Ross]

Hello Mehul Jaganiya,

Thanks for follow up information.

Are there any restrictions regarding the use of VBA macros on your network?

[Mehul Jaganiya]

I am not too sure about that, but I do use a lot of other VBA macros and have never had any problems.

[Leith Ross]

Hello Mehul Jaganiya,

Okay, I will put some code together and we'll see what happens.

[Mehul Jaganiya]

Thanks Leith

[Cerbera]

Hi how about this code:

Please Login or Register  to view this content.

I have it running from a workbook called RESULTS.xlsx - you'd want to change that in your code to match your controlling / results work book. Also update the range to be copied over and the name of the directory.

Leith may be able to improve on this as he's simply put "better" at it than me!

[Leith Ross]

Hello Cerbera,

The only thing I was planning to differently, for the sake of speed, was to access the data with the workbooks closed.

If speed isn't issue then your code will work fine.

[Mehul Jaganiya]

Thanks for your help, Cerbera.
I noticed that the code uses a range of A1:B3. However, I don't want to copy a range. I want to copy only two cells which are C4 and O17. Can I still do it with this code?

[Mehul Jaganiya]

Also when I ran this code it opened each file in the folder and asked me if I want to save any changes in each file. Is there a way of doing this without opening each files?

[Cerbera]

Hi Mehul,

I've tweeked the code for the two different cell references:

Please Login or Register  to view this content.

The code could be improved upon, e.g. storing the "current workbook" name rather than hard coding it.

I'm sure Leith can create something more elegant and efficient, but I fancied giving it a crack, even if it isn't the greatest

[Mehul Jaganiya]

Thanks Cerbera for addressing the range issue. Can you please also make it able to not open each file in the folder?

[Leith Ross]

Hello

You can easily prevent the dialog from appearing when closing the workbook by setting the argument SaveChanges to Flase, like this...

Please Login or Register  to view this content.

[Mehul Jaganiya]

Thanks. That solved the problem. Now the save dialog box is not opening anymore.

After running the macro excel processed it for about 30 secs but no data was copied from the files. It is all blank.

[Cerbera]

Hi Mehul,

Put some breaks in the code and monitor the values and position to see what is happening at each point. You should be able to check which workbooks are opening and whether it is selecting the correct data, etc.

Also you can add message boxes to show values as the code is processed, e.g.

Please Login or Register  to view this content.

[Mehul Jaganiya]

Hi Cerbera,

I am very new to the whole macros area and I am not too sure how to put breaks in the code.

One thing I noticed is that while the marco is running and going through all the files the active cell doesn't automatically move down the row. My understanding was that we have some sort of counter that makes the active cell to move down every time it loops.

Please correct me if I am wrong.

[Mehul Jaganiya]

I found a code which does almost what I am after. The only problem here is that it can not access the file names with symbols like "Simon's Boilers", "Baker's extra", "Sauer's bakehouse" etc.
Is there a way to fix this thing? Other than that it is all good.

Please Login or Register  to view this content.