Upload Multi Excel spreadsheets+Append Table

dspa0712

New Member
Joined
May 4, 2007
Messages
31
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"
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

zraj07

Board Regular
Joined
Jun 15, 2006
Messages
80
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

Board Regular
Joined
Jun 15, 2006
Messages
80
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"?
 

Forum statistics

Threads
1,181,717
Messages
5,931,651
Members
436,796
Latest member
parthibanmuniswamy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top