scattered data import into on worksheet

[GyverX]

I have been researching the solution to this problem for several days with no results that match my problem. I am hoping it is possible and I have come here in hopes that someone will know.

I am using Excel 2007 but have access to Excel 2010.

My boss has been using an Excel sheet as a template to fill out a sheet with information on it. This template is not actually a template in the terms we understand. They basically designed a sheet to look like a form and then saved it and then filled it out each time then needed it to and saved each file on its own.

They (We) have an estimated 300,000 files going as far back as 2005.

What I am wanting to do is create a database. (Im using Access 2007) but I want to grab as much information as possible from these sheets. The data is all over the place, but if I can get the data I need into columns, then I can sort out what is correct and what is not and then port that into a database so I do not have to recreate over 10,000 vendors.

There are 28 cells that have info in them. I need to get that info in to there own columns so I can sort them and clean things up from there.

The question is...

Is it possible to get the info from these files into columns in one or a couple worksheets?

Thanks for reading.
I am willing to answer the questions needed to get the answer.

 

[ChrisM]

Are the 28 cells in the exact same layout in all 300,000 files? By layout I mean cell location/address.

 

[GyverX]

Are the 28 cells in the exact same layout in all 300,000 files? By layout I mean cell location/address.

For the most part yes, They are separated by year right now, with some small changes but If I can get it to do it once then changing the code should not be that hard to accommodate the difference.

I have a membership to lynda.com so I have that resource but im still looking through the Excel info to see if the info is in there.

 

[ChrisM]

If they're all in the same place, then this is a fairly simple matter of using the FileSystemObject to loop through all files in a folder(s), then you open each file, pull the 28 cells you need, and write them either to a temporary array or to a blank worksheet, one row at a time. But I don't understand what you mean about changing the code, if there are 300,000 files how are you going to know which are in layout #A vs layout #B?

 

[GyverX]

If they're all in the same place, then this is a fairly simple matter of using the FileSystemObject to loop through all files in a folder(s), then you open each file, pull the 28 cells you need, and write them either to a temporary array or to a blank worksheet, one row at a time. But I don't understand what you mean about changing the code, if there are 300,000 files how are you going to know which are in layout #A vs layout #B?

Well, If I am going to to it file by file then when the rows dont match up then I know I have to change the cell arrangement.

As far as changing the code, I was looking at some coding to pull all the info from multiple files. If the information that was being pulled from the files were wrong then I would only have to change the cell coding to get the right cells. Most of the data is in the right place for all them. There might be a cell difference from a handful of files.

I will try what you have suggested, Thank you very much.

 

[GyverX]

I do believe that I have found a solution with out coding. I ran across an Excel add-on called "RDBMerge" at RDBMerge, Excel Merge Add-in for Excel for Windows This is a great and small add-on that does exactly what I needed.
In order to what I needed from specific fields I typed in the cell number instead of a range eg; "b1,b5,h22 ect." For those that come across this thread, I wanted to let them know what I found.

Just to give an Idea of what I have done so far.. The info I needed I did get. I just did a merge of 1 out of 5 employees of files from 2005 to 2012. I came up with 7703 rows. I did the operation on my laptop so I could leave it alone. This uses a macro type of operation so you have to consider that Key strokes are being operated so you can really do anything until its done or you will mess the operation by using your keyboard.

I hope this helps someone.
Thanks
Jason