How do I correct "Type conversion Failure" in Access 2007

[BigK]

Importing database from Excel 2007 and get "Type Conversion Failure" in a
couple columns containg numbers. Have tried changing the fields data type to
text, etc. No luck.
Help?

 

[Piet Linden]

Importing database from Excel 2007 and get "Type Conversion Failure" in a
couple columns containg numbers. Have tried changing the fields data typeto
text, etc. No luck.
Help?

Try creating a table by linking to the Excel file and creating an
empty table. Then fix the columns that are not working, then run the
append.

 

[scamquist]

Access looks at the first row(s) to determine what the rest of the column contains. If it sees all numbers, it will import it as a number. If any subsequent rows are text, the import will fail on those records. You can insert a dummy row in excel under the header column. If a column should be text, have the cell in the second row be text. Once imported, delete the dummy row from your table.

I use the vb code in Access to open the workbook, insert a row and set text or numbers in the second row.

Private Sub InsertDummy()
'--------------------------------------------------------------------------------
'When Access imports a spreadsheet, it looks at the values in the first data row
'to determine the format of the rest of the rows.
'In my case I need columns A and B to be text and columns C and D to be numeric.
'This code opens the Excel spreadsheet to be imported into Access and inserts a 
'row below the header row.
'It then inserts AAA in cell A2, BBB in cell B2, 1 in cell C2 and 10 in cell D2
'When complete, the workbook is saved, closed and Excel exits
'--------------------------------------------------------------------------------
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Set xlApp = New Excel.Application                                        'Open Excel workbook
xlApp.Visible = True
Set wbExcel = xlApp.Workbooks.Add
Set xlBook = xlApp.Workbooks.Open("full path to your spreadsheet")        'ie "C:\My Documents\Data To Import.xlsx"
xlApp.DisplayAlerts = False
Workbooks("Data To Import.xlsx").Activate
        Sheet1.Activate
        Sheets("Sheet1").Activate                                        'Sheet1 should be the actual name of your work sheet
        Range("a2").Select
        ActiveCell.EntireRow.Insert                                        'Insert a row below the header
        Cells(2, 1).Value = "aaa"                                          'begin adding text and numbers as required
        Cells(2, 2).Value = "bbb"
        Cells(2, 3).Value = 1
        Cells(2, 4).Value = 10
xlBook.Save
Workbooks("Data To Import.xlsx").Close
xlApp.Quit
xlApp.DisplayAlerts = True
Set xlBook = Nothing
Set xlApp = Nothing
End Sub