Automating the import of Excel data (1 Viewer)

[PaulA]

Greetings!

I am looking to automate the importing of Excel data to a specific table in an Access database.

The issues:

1. The data would be submitted by multiple persons (probably to a shared drive) in files with multiple file names on a monthly basis.

2. The persons submitting could vary from month to month (new hires and terminations)

I am familiar with the "Transfer Spreadsheet" function which could be used multiple times for each pre-designated file name--but I was wondering of any way to have more flexibility in the automation.

Any suggestions, or other help would be much appreciated!

Thanks.

 

[The_Doc_Man]

Paula, the method I use has the ability to allow anyone with the appropriate WorkGroup rights to do the import, but it involves some VBA code. I'll summarize the method.

I create an application object for Excel (see CreateObject)

When I have the object, I use the .Open method on it to open a workbook file. The name of the file can be supplied as a named parameter to the .Open method. In my case, they use an input box, but there is no reason you couldn't do a Browse dialogue if that is how you wanted to go. (The Browse feature is supported by the COMDLG32.OCX file, or Pat Hartman has an article that shows how to do this for COMDGL32.DLL.)

Once the workbook is open, you can select a worksheet, again by name, using the object.WorkBooks(1).WorkSheets("sheetname").Activate method.

After that, accessing the cells of the worksheet is trivial. The ActiveSheet.Cells(row,column).Text sequence gives you random access to each cell. Note that you might get back a null sometimes, so function Nz is your friend here.

In my case, I open a recordset to my table, then in a loop I do some recset.AddNew methods, one for each populated row in the spreadsheet. I directly address the columns in each row to populate the fields in my recordset. When each row is done I use the recset.Update method to write it out.

I have some tests to assure that the next row in the sheet is a good one, and I just skip any rows that look wrong. I have a separate test for the entire row being blank. For my case, that is the sign that I am done. So I close the recordset, close the workbook (with save option = False), and shut down whatever else was open that no longer needs to be.

All of this can be placed in a simple subroutine that is called when someone uses a button-click on the control button of your choice.

I know this is brief, but I have an aversion to publishing the entire code segment. It contains details that would only confuse you because these details are specific to my site.