Excel to Access Cell by Cell

[guybrush]

A Query:

For fun, I am going to help an accountant with this little project....

Companies submit their financial statements (Balance Sheet, Income statement etc.) to the state using microsoft excel templates. These templates are later available for download by the general public.

I have downloaded 800+ financial statements, and placed them in folders. Each excel file has a different name, though they really contain the same sort of information (I am assuming that the template used was not changed in any way i.e. tab names and cell locations).

I would like to build a quick module to iterate through these excel files, and populate the data into Access tables. I have worked with ADO and DAO, and have no problems importing spreadsheets when they are layed out all snazzy (csv, or otherwise). I have never on the other hand, tried to grab arbitrary data from excel, and stuff it into access.

I envision using VB to open the file, go to the specific hard coded cells, and read the data using ADO into the table.

Any tips on how to work with the excel portion in code? Also, should I be polishing up on my DLL calls for stepping thorugh the folder tree?

Thanks

 

[dk]

Hi,

This sounds like a fairly meaty project, but definitely doable.

Here's a few resources which may help you:-

1. To iterate through all files in your folder you could make use of FileSearch object which is part of Office. Type FileSearch into the VBA help. You could also use the FileSystemObject of the MS Scripting Runtime. Personally, I'd go with the FileSearch method because it's faster and doesn't require recursive programming if you want to search subfolders.

2. Extracting data from an Excel file using ADO is not hugely different than getting it from any other data source. See these pages for some examples:-

Connecting to Excel using Jet

Microsoft's example


I'd say that the iteration part should be easy. The tricky part would be identifying the correct data and then inserting it into your database.

Post back if you get stuck

 

[guybrush]

Thanks for the advice,

The File Search object worked perfectly. I was able to open a session with the excel files and extract the needed data.

I did run into a problem that I am brainstorming on. Perhapse somebody has an idea,

I assumed above that each excel files were layed out the same, which is most often the case, However, there are a number of files that did not conform to the template given. (The provided excel template was altered.) I assumed that the data would always be on the same row and column, which I have found is NOT the case.

Long story short, I am thinking about stepping through all of the files, and "weeding out" those that did not conform, and treat those as exceptions. I have a sinking feeling that this will yield 400 of 900 spreadsheets that did not conform. I am not about to go through each by hand.


Any thoughts on the subject are appreciated.