Import Excel Table to Access 97 errors

[obub]

I am trying to import an excel spreadsheet to an access 97 database. I am using the Import Spreadsheet Wizard, importing the Excel table to a new access table, however, there are a couple of fields (i.e. account number) that do not get imported due to Type Conversion Failure. I format the account number cells in Excel as text, but when I use the Wizard it changes the data type from text to Double. It will not allow me to change that data type, and as a result, it does not import some of the account number records. I have tried to import the spreadsheet into a table already formatted in Access, have tried using the TransferSpreadsheet, "Get External Data" from File menu, formatting the account number cells in Excel as custom, general, exporting the data from Excel to Access, but no luck. It is the same account numbers that do not get imported.

Please help, I do not know what else to do.

 

[dquin656]

Do the "problem" account numbers contain any spaces or special characters? I know this sounds "stupid", but if you can, re-type a few of the "problem" account numbers and then try importing the data into access again.

My other question, is are you using the same version of Excel and Access? I am not positive this would be an issue, but if you can, be on the same version of the software for both Excel and Access.

I have often had these types of problems when importing data, and it seems to be something stupid that can be fixed by manually overriding the problem records.

I hope this helps.

Debra in Chicago

 

[obub]

Debra,

Thanks for your quick response.

I am using Excel and Access 97. There are no other characters in the cells with the account numbers, and I double clicked on the cells in question in Excel, and when I imported the spreadsheet in Access it took the values in question. There is one problem though, and maybe I should explain the background of what I am trying to do.

Someone receives a weekly Excel Spreadsheet and I wrote a macro that eliminates any rows and columns not needed. Than that file is saved with a different name. I am working on an Access module that imports that spreadsheet into a table, then I want it to randomize the order of the records and assign the records in the table to 3 different people. That part I have done before, I just can't get the data to import correctly. I cannot have someone double click on each cell in Excel before they save the spreadsheet for fear of data corruption (i.e. they delete something by accident).

I need all this to be automated, so all that person has to do it push a couple of buttons. I am running out of ideas on how to fix this data import.....

 

[slreynolds]

One approach is to create the table with all field definitions as required, then delete all of the records and re-import them in from the spreadsheet each time.

In the code below:

tblUpdate = Access table to contain Excel data
input.xls = Excel spreadsheet data comes from (you will need to change the drive & directory)
RangeName = name of range within Excel spreadsheet to be imported

*******************************

Private Sub Import_Click()
' Open the tblUpdate table and delete all records
On Error GoTo xlsForms_Err
DoCmd.OpenTable "tblUpdate", acNormal, acEdit
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDelete
DoCmd.Close acTable, "tblUpdate"

' Import records from the Input spreadsheet to the tblUpdate Table

DoCmd.TransferSpreadsheet acImport, 8, "tblUpdate", "c:\input.xls", True, "RangeName

xlsForms_Err:
MsgBox Error$

End Sub

Susan

 

[obub]

Susan,

That would work once I was able to import the spreadsheet into Access. I cannot get Access to import the spreadsheet whithout leaving a few account numbers blank due to Type Conversion Failure. I have tried importing the spreadsheet into a new table, and an already set up table, with no luck....

 

[oharab]

A method I have used in the past is to use automation to save the excel file as a temporary text file, then import the textfile into access which allows me to use import specifications, then delete the temporary text file.
It's a bit longwinded, but it works and it's pretty quick.

B ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------

 

[obub]

Will try, thanks