Converting 1000s of spreadsheets to one access file

[vangogh228]

A group in my company has, for quite a while now, used a spreadsheet template to capture information needed for customer hard output. Someone built a page, with the less-than-a-dozen required cells in the appropriate places and a set print area, for printing the information. After they enter the information and print the page, they save the excel file. They have been doing this for quite a while now, and the number of files they have on their shared drive is staggering... and the method they using for naming conventions and cataloging them is almost beyond comprehension.

As they do indeed refer back to these files quite a bit, I am wondering if it possible to import the required cells of information from this mass of excel files, all of which are formatted and laid out the same, to an access table. I have never considered this before and have no idea how to start or whether it is even possible. I did a search on "import" but no results match what I am considering here.

To clarify one thing about these files: The information is not contiguous. It is spread all over... C6, E12, F45, J5, etc. They tried to build the form in excel so that it would print in the format they needed.

Thanks for all your help!!

Tom

 

[The_Doc_Man]

First, search this forum for "Opening an Excel Object." Also search the Access Help files on this topic. While you are at it, look at the File System object in your help files. Routine GetFolder comes to mind. There is also the FileFind object which has a similar ability. Lots of things to see in the help files. Once you are done with that, read this stuff below.

OK, here is an overview of what is facing you. To do this job, you need to know where the spreadsheets are so you can define a beginning folder for a file search.

The file search will involve finding each spreadsheet in turn, opening it as an object, extracting the data (detais to follow), updating whatever tables are involved, and closing the object completely. Search this forum for topics such as "Excel won't close" and such to see how folks have dealt with opening and closing multiple objects in a single session.

OK, for each spreadsheet, you open the Excel application object, then use a method for that object to open each file name. (You got the names from the .FilesFound collection from a FileFind or GetFolder or something like that.) You open the workbook using a method described in the EXCEL Help files, associated with opening workbooks. You find the sheet that is populated and make it your active worksheet. Then you start random-accessing the things you want to find. You said the format was ugly but fixed in position. That makes this possible.

An Excel spreadsheet has a series of collections. There is a collection called Rows for which the columns are selected as

ActiveSheet.Rows.Cells(m)

The corresponding situation for columns is that a collection called Columns contains rows selected as

ActiveSheet.Columns(m).Cells

And EITHER of these gets you cell nm. BUT the catch is that this syntax uses numbers for BOTH selectors. So cell A1 is really Row 1, Column 1. There is no Column "A" in this usage.

Cells contain a .Text segment but they have other attributes. I would strongly suggest reviewing the EXCEL help files for their comments on VBA programming and accessing the contents of a cell.

OK, so you can access the cells. Now what? Outside the file search loop, you should open a recordset to your table that will hold all of this information. For each file (which you imply holds data equivalent to one and only one person record), do a recordset.AddNew, define the fields of the open record in the recordset, and do a recordset.Update to assure it is stored.

When you run out of files, close the recordset and release the objects. One way to release objects is to Set object-name = Nothing. But always close the object BEFORE releasing it. Otherwise you will run out of something - like resources, memory, handles... who knows which one will barf first? I don't.

If you are not comfortable with VBA then you have no really easy solution and perhaps should practise your VBA first.

 

[Dreamweaver]

This topic may be of some help
http://www.access-programmers.co.uk/forums/showthread.php?t=118612

Plus I'll Add The Find Files Of Types The_Doc_Man Talked About

Mick

 

[vangogh228]

Doc Man: WOW. Thanks. I didn't expect all that... but it certainly is appreciated. I will review those items you indicate and then follow your instructions carefully.

Doc Man and Dreamweaver: Thanks for the tips on the searches, and the link. Sometimes, the biggest trouble is knowing what to search on and your references will be a great help.

Thanks again. It looks like I have some work ahead of me.

Tom