Importing data from multiple Excel files vi code.

[slang]

Hey there.
I am really new at Access but know enough to be dangerous in VB:rotlaugh:

I built an application in Excel that 40 reps use in the field and update to a shared drive here at the mother ship.

I had an Excel app that amalgamated them all into one master sheet but I want to have it in Access so I can access the reporting functions of MSACCESS.

Been a long time since I have been in a database (dbase3):bug: and I am thinking about an append query? But I just want to rebuild the main database everytime so I get all the data and not keep appending the same over and over again.

Any help in where to start and I can probably muddle through it.:doh:

All the sheets have a named range and the same data fields and formats.
Thanks in advance and tgif:beerchug:
</IMG>

[CreganTur]

I had an Excel app that amalgamated them all into one master sheet but I want to have it in Access so I can access the reporting functions of MSACCESS.

That's very good- multiple users should never be writing to the same backend Excel sheet anyway- WAY too many possibilities of write errors and other problems.


But I just want to rebuild the main database everytime so I get all the data and not keep appending the same over and over again.

If you properly setup your tables with Primary Keys, you can easily keep duplicate entries from being created. There is no reason to rebuild the database tables every time, especially if you are trying to create a database that holds historical records.

You can import records into your database using the TransferSpreadsheet method- just be sure to use the acImport parameter. You can use a file dialog window to allow your users to select the file they want to import from. Search this Access forum, you'll find a number of examples on how to do this.

[Kicker]

I have a similar problem. My excel workbook has 7 worksheets and I only need to import a single sheet which is Sheet7 and has the name "Raw Data". It is not the first sheet in the workbook. How do I import just the data from the single worksheet?

[CreganTur]

You can define which worksheet to pull from in the Range parameter of the TransferText acImport method. I don't remember the exact syntax, but google will tell you.