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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

Import Excel Spreadsheet into Access via VBA without Error table

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
flyguy80
Asked:
flyguy80
1 Solution
 
Patrick MatthewsCommented:
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
 
ldunscombeCommented:
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
 
NorieData ProcessorCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now