Importing Excell Spreadsheet into Access

[Guest]

I was asked to import an Excell Spreadsheet into Access and design a
database, but the spreadsheet has more than one workbook, 118 to be precise,
is there a way to get all these workbooks into one Table in Access, I am
fimiliar with the
importing of data but I dont have the know-how to get all this on to one.

Any help will be appreaciated.

 

[Guest]

You can import only one worksheet at a time. The most straightforward way to
to this would be to create two tables in Access. One would be the permanent
table that will hold all the data from the 118 worksheets, and one temporary
table that would be used as an intermediate to receive the data from the
worksheets. You will also need to create an append query that will move data
from the temporary table to the permanent table.

The do the following:
1. Delete all the data in the temp table
2. Import a worksheet into the temp table
3. Run the append query to move the data into the premanent table.

Repeat this process for all the worksheets.

 

[Guest]

I am doing a similar thing using the TransferSpreadsheet Method of the DoCmd
to export and then import. What I am having difficulty with is how to
suspend Access while the Excel sheet is being updated. How do you handle
this ?

 

[Guest]

I am not sure I understand the question. Are you saying that you are trying
to import an Excel sheet that a user has open?