Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

import multiple excel sheets to multiple tables

Posted on 2008-11-06
30
Medium Priority
?
439 Views
Last Modified: 2013-11-27
I have some excel workbooks that have multiple sheets that I need to import into Access.  These worksheets are all different and the fields in the tables they need to populate may not be in the exact order as on the sheets.  What would be the best way to accomplish this?
0
Comment
Question by:nfstrong
  • 14
  • 14
  • 2
30 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22897442
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "TableName", "FileName", True, "Range"

Access Developer Reference
DoCmd.TransferSpreadsheet Method

The TransferSpreadsheet method carries out the TransferSpreadsheet action in Visual Basic.expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
expression   A variable that represents a DoCmd object.

Parameters

TransferType Optional AcDataTransferType The type of transfer you want to make. The default value is acImport.
SpreadsheetType Optional AcSpreadSheetType The type of spreadsheet to import from, export to, or link to.  
TableName Optional Variant A string expression  that is the name of the Microsoft Office Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Access select query  whose results you want to export to a spreadsheet.
FileName Optional Variant A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.
HasFieldNames Optional Variant Use True (1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.
Range Optional Variant A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
UseOA Optional Variant This argument is not supported.
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22897454
The fields do not have to be in the same order as the headers on the worksheets.
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22897478
acSpreadsheetTypeExcel12 = Excel 2007
acSpreadsheetTypeExcel11 = Excel 2003
acSpreadsheetTypeExcel10 = Excel 2002
acSpreadsheetTypeExcel9 = Excel 2000
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:nfstrong
ID: 22898357
> The fields do not have to be in the same order as the headers on the worksheets.

But do they need to have the same field names?
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22898402
Yes, they do.
0
 

Author Comment

by:nfstrong
ID: 22898424
ok, what if they don't?
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22898432
It won't work.
0
 

Author Comment

by:nfstrong
ID: 22898450
Is there another method to use besides TransferSpreadsheet to import the data since the field names don't match?
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 800 total points
ID: 22898452
nfstrong said:
>>ok, what if they don't?

Then you either:

1) Set up the Excel worksheet as a linked table, and then use a query to append the data from the linked
table to the destination table, or

2) Import the Excel data into a staging table, and use an append query to get the data from the staging
table to the destination table.
0
 

Author Comment

by:nfstrong
ID: 22899122
matthewspatrick,

I tried your option and I receive the Microsoft can't append all records error.  What causes this?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22903782
nfstrong said:
>>I tried your option and I receive the Microsoft can't append all records error.  What causes this?

My first guess would be a data type mismatch.  Check the data types in Access against the data types in'
the worksheet(s).
0
 

Author Comment

by:nfstrong
ID: 22904823
I went through and matched all of the data types and I'm still receiving the same error.  Any other thoughts?
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22904870
Please post the complete error description.
0
 

Author Comment

by:nfstrong
ID: 22904921
Here is the error.
AppendError.doc
0
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 1200 total points
ID: 22904974
Your problem is that the data you are appending violates a key in the database. For example, if you have a field set to a primary key (no duplicates) and you append a record with a value in the primary key field that is the same as a record that already exists, Access will not allow you to append that record.
0
 

Author Comment

by:nfstrong
ID: 22905022
There are no duplicates in the data I'm trying to append and the target table is empty.
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22905076
What are the attributes of each fiel in your table?
0
 

Author Comment

by:nfstrong
ID: 22905192
ABCTest is the imported Excel data and tblFinSumNetInc is the table I'm trying to append to.
AppendTables.doc
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22905288
Which fields in ABCTest are you appending to which fields in tblFinSumNetInc?
0
 

Author Comment

by:nfstrong
ID: 22905379
Column A is ABCTest table fields being appended to column B tblFinSumNetInc fields
AppendFields.xls
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22905548
The types and sizes line up. What indexing do you have on the tables?
0
 

Author Comment

by:nfstrong
ID: 22905597
Here are the indexes
AppendTablesIndexes.doc
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22905733
I don't see any problems with these. How many records are you trying to append?
0
 

Author Comment

by:nfstrong
ID: 22905749
only 33 with this one.
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22905848
And they all fail. Hmmm. Try entering one record into the tblFinSumNetInc table manually.
0
 

Author Comment

by:nfstrong
ID: 22906083
Ok, I entered a record manually and received an error that there must be a record in tblExecSum, which is the parent table.  That makes sense.  So I entered the primary keys needed in tblExecSum and reran the append query.  I get the same error, but it does append the 33 records.  Why would it still show that error?
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22906106
Delete the data in the table and try to enter one record manually again.
0
 

Author Comment

by:nfstrong
ID: 22906182
I input a record manually and don't receive any error.  I deleted that record and reran the append query and it ran through just fine.  Not sure what the error was about.
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 22906199
I'm glad it is working for you now. You might want to consider a query that will update tblExecSum table before appending the new records to the tblFinSumNetInc table.
0
 

Author Comment

by:nfstrong
ID: 22906211
I will do that.  Thanks for all your help.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question