How to collect data from multiple files

[novoiceleft]

Is there a way to aggregate data from multiple Excel files in a folder?

I have Excel file ("A"), which is a single worksheet which looks like an input form. It is quite a complicated sheet, with many types of Dropdowns and data validation. I have multiple users, who will take the blank worksheet, fill-in the data, and file it in a folder.

I have also built another complex Excel analysis worksheet ("B") which aims to collect and analyse the data from the multiple "A" files. The data it needs is always in the same cell positions in A files.

As an example, my formula in "B" wants the SUM of all the cells called B7 in every file in the folder. But it does not know how many files there are and does not know their names.

Is this even possible? Am I going about this the wrong way?

Maybe it involves some programming - but I am not a programmer !!

By the way, all this is in a Sharepoint folder online - but I doubt that makes any difference to the basic problem.

Any help appreciated

NoVoiceleft.

 

[KeithG]

You would have to use VBA to do this.

 

[scott-atkinson]

Even if you do code this in VB you would need to know the folder names, and how many folders there are.

I think before you even start looking at coding, I think you need to create a naming convention process with all the shared users, so that they define their folder paths and names, and that you have a true reflection on the number of folders in use.

Only then can you start looking at coding, as you will need to reference each folder by name to retrieve its relative data.

 

[unmarkedhelicopter]

Only then can you start looking at coding, as you will need to reference each folder by name to retrieve its relative data.

No, you would only need to know the base folder, you could then scan through all sub-folders to look at files. This assumes that ALL the found files were of the requisite type, if not you'd have to have some "file identifier" or the system would skip that file.
It's doable but it's a lot of work and not the sort of thing that is covered under basic advice, you would probably be better off hiring a coder and then you'd get some support for it too.

 

[KeithG]

Even if you do code this in VB you would need to know the folder names, and how many folders there are.

As long as you know the base folders path you can use the File System Object iterate through all files and folders.

 

[DanG]

I do something like this at work. I have a "master" file and it gathers information from 60 sheets. When I want my master file updated I just paste the new sheets into the folder and overwright the old ones.

You can do this if your results are numbers (counting or summing) very easy. I use the sumproduct function as it is the only function I know of that does not require the referencing sheets to be open. If you would like more info let me know.

 

[F0ur2o]

Thanks for your quick replies everyone.

The base folder would always be the same. ie c:\testresults\*.xls

The naming would be very similar.

Here is the thread where i found the first code I tried -- and failed.

http://www.ozgrid.com/forum/showthread.php?t=71552

And this loop code seemed more relevant as it did not seem to require any file naming and would run through a folder and process all XLS files.

http://www.ozgrid.com/VBA/loop-through.htm


anyone think these VB codes will work on my form.

I seem to get stuck here....obviously where I need to know more.

If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

'DO YOUR CODE HERE

wbResults.Close SaveChanges:=True

Next lCount
End If
End With


Thanks again for any help.

 

[F0ur2o]

Oops -- wrong thread -- sorry.

 

[qafself]

Yes, it can be done but needs VBA.

Beyond my skill level - if you get no joy here suggest you post in

http://www.excelforum.com/

or

http://www.ozgrid.com/forum/forumdisplay.php?f=8

Remember to include a reference to your original thread here

Regards

Ed