Importing from Excel To Access - Multiple Worksheets :-(

[Ady]

Hi There

Using Office 2000
=============
I have an access database. I am trying to import data from an excel
spreadsheet into the DB via a macro. Within the spreadsheet I have 2
worksheets:

Start Date Jan 2000
Blanket Orders

Both worksheets within the spreadsheet contain the same column headings. In
access I have created a macro that will automatically import and create a
table for each worksheet:

Table: Start Date Jan 2000
Table: Blanket Orders

However what I am really trying to do is to merge all the data together in
one table (via macro). Is this possible? The data is in the same format on
both worksheets.

Thank you in advance for any help offered. All very much appreciated!

Regards

Ady

 

[Bob Finster]

You can create an "Append Query" to add the data from the
second worksheet to the first table. This will add the
data to the end of the table.

You can actually link to the Excel Spreadsheet(not sure
about each worksheet) and then use a "Make-Table Query" if
the table doesn't exist for the first worksheet then run
the "Append Query" for the second worksheet. This will put
the data together.

 

[Ady]

Many thanks for your response. I tried the Append Query option and even
though all the columns are the same in both worksheets I get the following
error message:

Microsoft Access set 319 field(s) to Null due to a type conversion failure,
and it didn't add 0 records to the table due to key violations, 0 record(s)
due to lock violations, and 0 record(s) validation rule violations.

I probably couldn't get it more wrong if I tried to!!!!!!!

If it is any help here is the SQL view:
INSERT INTO [Blanket Order List] ( [Job No#], [Order No], [Part Number],
Description, QTY, Customer, [Date Order Rec'd], [Date Sent], [Due Date],
[Price Each], [Total Value], [Issue/cmnts], [Stock Amount] )
SELECT [Sales Order List].[Job No#], [Sales Order List].[Order No], [Sales
Order List].[Part Number], [Sales Order List].Description, [Sales Order
List].QTY, [Sales Order List].Customer, [Sales Order List].[Date Order
Rec'd], [Sales Order List].[Date Sent], [Sales Order List].[Due Date],
[Sales Order List].[Price Each], [Sales Order List].[Total Value], [Sales
Order List].[Issue/cmnts], [Sales Order List].[Stock Amount]
FROM [Sales Order List];

Thank you in advance for any help offered. All very much
appreciated!(Especially Bob!!)

Regards
Ady

 

[finster26]

I am not totally sure of this. It looks like the fields in
Excel do not match the type in the Access Table. Here is a
way to test this.

If everything is setup right, you should be able to
manually copy the range of cells in Excel that match the
dimensions(fields) of the table in Access. Once you have
copied the Excel cells, go to the Access table, select
range entire row for a new record by click the far left
where the asterisk is. This click paste. If everything is
setup up correctly is will paste in. If not you need to
check the format of each cell in excel. Including the
header with the field names.

You may also have data validation rule for a field in
Access that is not allowing the data to come.

You SQL statement looks to be correct.

-----Original Message-----
Many thanks for your response. I tried the Append Query option and even
though all the columns are the same in both worksheets I get the following
error message:

Microsoft Access set 319 field(s) to Null due to a type conversion failure,
and it didn't add 0 records to the table due to key violations, 0 record(s)
due to lock violations, and 0 record(s) validation rule violations.

I probably couldn't get it more wrong if I tried to!!!!!!!

If it is any help here is the SQL view:
INSERT INTO [Blanket Order List] ( [Job No#], [Order No], [Part Number],
Description, QTY, Customer, [Date Order Rec'd], [Date Sent], [Due Date],
[Price Each], [Total Value], [Issue/cmnts], [Stock Amount] )
SELECT [Sales Order List].[Job No#], [Sales Order List]. [Order No], [Sales
Order List].[Part Number], [Sales Order

List].Description, [Sales Order

List].QTY, [Sales Order List].Customer, [Sales Order List].[Date Order
Rec'd], [Sales Order List].[Date Sent], [Sales Order List].[Due Date],
[Sales Order List].[Price Each], [Sales Order List]. [Total Value], [Sales
Order List].[Issue/cmnts], [Sales Order List].[Stock Amount]
FROM [Sales Order List];

Thank you in advance for any help offered. All very much
appreciated!(Especially Bob!!)

Regards
Ady

You can create an "Append Query" to add the data from the
second worksheet to the first table. This will add the
data to the end of the table.

You can actually link to the Excel Spreadsheet(not sure
about each worksheet) and then use a "Make-Table Query" if
the table doesn't exist for the first worksheet then run
the "Append Query" for the second worksheet. This will put
the data together.


the
data together in much
appreciated!

.