Type Conversion Failure With Excel Data Import

[OasisUnknown]

Hello All,

I am having an issue with a type conversion error With some of my data on an excel import.

All of my fields in all of my columns on the source data for excel are set to a format type of text.
In the cells is a date in military format (ie 20160809) So excel wants to treat it as a number even though I have told the cell to treat it as text so it does give that little green icon that says there is a error in this cell.

but I want the data to just be read as text.

When I import the data into Access it gives a type conversion error on this field even though I have my data type set to short text.


The weird thing is it does not give the type conversion error on all the fields and intermittently will give the error on different columns.

I have about 7 columns that the data is this exact same set up and it will give the error on one or two of the columns but not all seven.

Any one have any ideas about what could be going on. I just want to take the data as is from excel and store it as text. I convert it to a normal date format with queries after the fact but I cant do that if the cells don't get imported.

I should also note that today it did it with a field that is text and not just a date. the data in the field looks like (38mo 14dys)


When I pull the data from the system I save it into an excel workbook with 4 other tabs (total of 5) and how I have been getting it to my database because I only need 1 of the 5 tabs is I right click on the tab and copy and move it to a brand new worksheet to break out the tab that I need and I import that new workbook into my database.

 

[xenou]

Re: Type Conversion Faile With Excel Data Import

Here's some sample code (For the dummy row stuff):

Sub foo()
        
    Dim xlApp As Object
    Dim wb As Object
    Dim ws As Object
    Dim i As Long
    
    On Error GoTo ErrHandler
    
    Set xlApp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Open("C:\myTemp\Test.xlsx")
    Set ws = wb.Worksheets(1)
    With ws
        Rows(2).Insert
        For i = 1 To 8 '//Columns 1-8 (A-H)
            .Cells(2, i).Value = "AABBCC" '//Second Row (assume headers in row one)
        Next i
    End With
    wb.Save

ErrHandler:
If Not wb Is Nothing Then
    wb.Close False
    Set wb = Nothing
End If
If Not xlApp Is Nothing Then
    xlApp.Quit
    Set xlApp = Nothing
End If

End Sub