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

  • Thread starter Thread starter BigK
  • Start date Start date
B

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?
 
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?
Click to expand...

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.
 
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.

Code: 
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
 
Back
Top