Find data in over 100 excel files

[jad007]

Hi all
I have been developing an excel spreadsheet to help me run my business (selling blinds). It will work out the prices for any type of blind when I enter the sizes. I have over 100 different jobs/files.

What I wish to do is have a different file that will extract information e.g. customer's name and phone number and if we got the job or not.

How can I get excel to look for this information in all these files without manually entering the file names?

I hope that I have explained this correctly?

Thanks in advance

Joe

[Richard Buttrey]

Hi,

Are all the files consistently laid out? For example is the name and phone number and other stuff you want to capture always in the same cells?
And are all the files in the same folder and do the file names have any common text.

If so you could have a macro that looped thorugh all the files and grabbed the data.
Longer term why are you keeping all this information in separate files rather than in a single sheet in one workbook as a proper database. It's always easier to extract information from a normalised two dimensional table rather than from data that which is scattered around.

Incidentally would you update your profile to show your location. This is often useful when we need to consider things like regional settings and date formats.

[jad007]

Hi,

Are all the files consistently laid out? For example is the name and phone number and other stuff you want to capture always in the same cells?
And are all the files in the same folder and do the file names have any common text.

If so you could have a macro that looped thorugh all the files and grabbed the data.
Longer term why are you keeping all this information in separate files rather than in a single sheet in one workbook as a proper database. It's always easier to extract information from a normalised two dimensional table rather than from data that which is scattered around.

Incidentally would you update your profile to show your location. This is often useful when we need to consider things like regional settings and date formats.

Hi there
Many thanks for getting back to me.
The reason why there are so many files is so that each file is a separate job. Each file is about 7mb+ (with pictures of the job if needed) we have alot of customers lol!
I have never even tried macro. Are they easy to understand?
I have updated my location ;-)
Many thanks
Joe

[alansidman]

Please address these questions that Richard raised

Please Login or Register  to view this content.

This is extremely important for us to know how to structure a solution for you. While Richard may offer up a Macro solution, I am thinking in terms of Power Query. Of course, this all depends on how you address the above issues.

[Pete_UK]

Alan,

before you get into that, it would be handy if the OP (Joe) updated his profile further to show the version of Excel that he is using, as it might turn out that his version doesn't support Power Query.

Pete

[alansidman]

Pete
Concur, but in any event it would be helpful no matter which way we go to have that information.

Regards

[jad007]

Please address these questions that Richard raised

Please Login or Register  to view this content.

This is extremely important for us to know how to structure a solution for you. While Richard may offer up a Macro solution, I am thinking in terms of Power Query. Of course, this all depends on how you address the above issues.

Hi there
Yes they are all in the same cells in every single one
Many thanks Joe

[Pepe Le Mokko]

Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile says 2003, but your file indicates at least 2007

Also

do not quote entire posts unnecessarily. They clutter the thread and make it hard to read.
Thanks

[jad007]

Please address these questions that Richard raised

Please Login or Register  to view this content.

This is extremely important for us to know how to structure a solution for you. While Richard may offer up a Macro solution, I am thinking in terms of Power Query. Of course, this all depends on how you address the above issues.

Hi there
I'm very sorry but I did over look this. Consider my hand slapped!
All done now
Many thanks
Joe

[jad007]

Hi there

Point taken thanks

Joe

[Richard Buttrey]

....
"And are all the files in the same folder and do the file names have any common text."

[alansidman]

If your files are all in the same folder and are arranged (formatted) the same then Power Query may be your solution. Look at this link for how to combine multiple files.

https://trumpexcel.com/combine-data-...ple-workbooks/

[jad007]

Hi there

Awesome.I will be having a play with this tonight and I shall let you know how I got on.

Many many thanks buddy

Cheers
Joe