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


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?
 
Ad

Advertisements

P

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

Advertisements

Joined
Nov 7, 2009
Messages
3
Reaction score
0
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top