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
Similar Threads
