Solved

Import Excel Spreadsheet into Access via VBA without Error table

Posted on 2010-08-23
3
524 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

815 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

8 Experts available now in Live!

Get 1:1 Help Now