Solved

Import Excel Spreadsheet into Access via VBA without Error table

Posted on 2010-08-23
3
523 Views
Last Modified: 2013-11-27
I wrote code in VBA to import an excel spreadsheet into Access. Now because it's formatted in a weird way in excel, i'll have to clean the imported table. I get a table with the errors of the imported data. Is there a way to eliminate the error table from being created? I have no use for it

' IMPORT UNUM EXCEL SPREADSHEET AND NAME IT tblUNUMIntermittentLeaveSummaryReportTemp
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblUNUMIntermittentLeaveSummaryReportTemp", "C:\Documents and Settings\T94HGEY\Desktop\eLeave\UNUM\Intermittent_Leave_Summary_Report(1).xls"
0
Comment
Question by:flyguy80
3 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33505241
flyguy80,To avoid the errors, you should load first into a "staging" table in which all of the columns use the text data type.  You can then use queries and/or code to perform your data clean up and move the validated data to your permanent table(s).Patrick
0
 
LVL 14

Accepted Solution

by:
ldunscombe earned 500 total points
ID: 33506928
What I have found is that using the transfer spreadsheet method to import excel data into a new access table will cause the new table to be created with the field types matching the first row of data that in in the spreadsheet.
So say for arguments sake cell A1 in your spreadsheet is a date, then the first field in the new table will be set as a date field.
Then If Cell A2 in your spreadsheet is text, a data type conversion error will occur because access is trying to insert text into a field it has determined should be a date based on the first row of data and an import errors table will be created.
To avoid this you can
A: Create a blank table in Access with the appropriate data types for the data that is to be imported and import to that table
Or B: Ensure that the first row of data in your spreadsheet is of a type that can accept all the data in that column.
For example
If Cell B1 contains 1234 then that will be determined to be numeric and any text in column B below row 1 will generate an error
However if Cell B1 contains ABC then the corresponding field in access will be set as Text Data type into which 1234 can also exist without error.
Hope this helps
Leigh
 
0
 
LVL 33

Expert Comment

by:Norie
ID: 33512055
Wouldn't/couldn't the error table actually help you?

Either with cleaning things up or identifying if there is a problem with the import.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help writing a query 6 73
MS Access XML Export Query Setup Multiple Tag Values 15 30
Close Print Preview button not active 12 36
Error in Rs.MoveNext & Duplication of Record 14 26
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now