Upload Multi Excel spreadsheets+Append Table

[dspa0712]

I have created an Access Database with Table called "data" and fields Names......

Provider
Service Name
Service Number
Primary Client Group
Capacity
Service Type
FTE Managers
FTE Staff

In the location
S:\Team\Contracting\Supporting People\Finance Monitoring\New Costing\Data

I have saved numerous excel spreadsheets all with different file names but with same structure.

On each individual spreadsheet in this location is a Worksheet name "master"

I want a msgBox to pop up in access asking "Do you want to upload new data?"

If the result of the question is Yes then the results of all the master sheets in the named location to be appended to the Access Table called "data"

 

[zraj07]

upload multiple excelworksheets and append

Go to the MACRO tab in Access. Build macros to import each spreadsheet. You could try using UNC naming convention in case other users who may be "mapped" differently have to do the task, but otherwise the path you stated could be used.
If you have 7 worksheets, the 7 macros to import. Then build an Append query, and go back to MACRO tab and add that Append query.

When you run the macro, it will import the worksheets into tables, and then append the results.

As for the command button, more difficult. You would have to think of a trigger, or some logic that would help you understand as a potential user with no knmowledge of your process, "why is the databse asking me if I want to imoport something"?

 

[zraj07]

upload multiple excelworksheets and append

Go to the MACRO tab in Access. Build macros to import each spreadsheet. You could try using UNC naming convention in case other users who may be "mapped" differently have to do the task, but otherwise the path you stated could be used.
If you have 7 worksheets, the 7 macros to import. Then build an Append query, and go back to MACRO tab and add that Append query.

When you run the macro, it will import the worksheets into tables, and then append the results.

As for the command button, more difficult. You would have to think of a trigger, or some logic that would help you understand as a potential user with no knmowledge of your process, "why is the databse asking me if I want to imoport something"?