Extracting Data from Multiple Files in a Folder

[endoskeleton]

Hi,

I dont even know if this is possible, but I'm sure you will tell me if it isn't.

I have an excel sheet that is generated daily (with a different files name each day). Each of these files is stored in the same folder on my drive.

Is it possible to create a sheet that will pull data from each of these files updating when new files are added to folder ?

The headers needed in this output file are:-

Amount / Currency Code / Processed Date / Release Date.

Let me know if I am dreaming or if this is a possibility?

Cheers

Jon

[snb]

No problem.

[endoskeleton]

That's good news snb.

Any idea how to actually do it ?

Cheers

Jon

[snb]

Tell us more:

which folder
what files
which filenames
post an example of one file
post an example of the resulting file, filled manually

But Im not sure whether your Mac supports VBA; you tell us..

[endoskeleton]

Hi Snb,

Please find attached the type of excel file that will be stored in the folder. The new file names will alter each day in terms of the number of transactions and the date.

The folder will be held in /Users/jonbowtell/Desktop/PCRV Daily Files

The output I need it to produce will be virtually identical to the summary sheet in the attached file, except that I do not require column D.

What I would like to happen is that when I run the macro it checks this folder for new excel files and upon finding them extracts the data from the new files (summary sheet) in columns A/ B / C and E and inputs that in a new row.

Any help and pointers would be appreciated.

Jon

[endoskeleton]

Any ideas appreciated, I have a customer pestering me for the results

Cheers

Jon

[abousetta]

Hi Jon,

Maybe I can help give you a push in the right direction, but I by far no expert like snb and others on this forum.

Questions:
Is the first worksheet what you are trying to accomplish and the other 3 sheets are the ones that will present in the other workbooks?

Will the names or order in each worksheet in the workbooks change or are they always the same?

How was the 'Summary' sheet populated? Which cells from the other worksheets provided the needed information to populate this summary worksheet?

I think these answers will help myself and other attempt to help you.

Good luck.

abousetta

[endoskeleton]

Ok

The workbook I posted has 4 sheets :-

1. Summary
2. Approved
3. Problem
4 "Name will change" but this is the initial raw data

The first 3 sheets are populated by a macro run on sheet number 4

Workbooks just like this will be saved in the directory I mentioned, each day they will have a different name, due to number of records and the date.

What I want to do is have a new workbook, that basically replicates the summary sheet, but each time a new file is found in the directory, it will populate a new record to the sheet.

It is in effect a summary of the summary (if that makes sense).

Cheers

Jon

[abousetta]

Hi Jon,

OK, I think I understand what you want but for clarification, you want a new workbook to open each excel file and copy the contents of the summary sheet to one consolidated worksheet. Is this correct?

abousetta

[abousetta]

Hi Jon,

I hope this can get you started:

Please Login or Register  to view this content.

This will loop through each file in a folder you designate. Now it needs to be tweaked to do what you want further than opening and saving files.

abousetta

[endoskeleton]

Many thanks, I will try this in the morning when I can think a little clearer.

My immediate thought is that if this has to open each file in order to get the data will it take a massive amount of time to run when I have 200+ workbooks files in that folder?

Or does this only open files that have not already been inputted into the master summary workbook ?

[abousetta]

This will open all files that have .xls in the file name in the folder you pick. Be it 1 file or 10,000.

I will also look at your problem with fresh eyes and see if I can customize this further.

abousetta