Data Import - Import into Text Formatted Table Field 1

[kjv1611]

I originally asked this question at:
thread700-1483500.

Does anyone have an idea as to what the cause would be?

I am creating several tables in code based on the worksheets that will be imported from Excel. I now have (it seems) everything working except for this one thing.

For some reason, even though I set every field as "text", Access is still creating "_ImportErrors" tables. I can't for the life of me figure it out.

Every time it is on the "House #" field. That field is storing the house number of addresses.

Here is the code I have that is working on everything except for the House # field - and it's just a few records that are giving the issue:

Public Sub ExcelToDatabase()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As Field
    Dim c As Integer 'Excel Column
    Dim intMaxCol As Integer 'Excel worksheet furthest right column with something in it
    Dim intMaxRow As Integer 'Excel worksheet lowest row with data.
    Dim strWbName As String 'Store name of workbook
    Dim z As Integer 'Test to create tons of extra fields so no headaches with imports
    Dim x As Integer 'To count through a few extra columns just in case (when matching up field names)
    Dim intCntTbl
    
    Set wb = Workbooks.Open(Forms!frmImport!txtXlFile)
    Set db = CurrentDb
    
    strWbName = Forms!frmImport!txtXlFile
    
    For Each tdf In db.TableDefs
        With tdf
            If InStr(.Name, "sys") Then
            ElseIf InStr(.Name, "tbl") Then
            Else
                DoCmd.SetWarnings False
                DoCmd.DeleteObject acTable, tdf.Name
                DoCmd.SetWarnings True
            End If
        End With
    Next tdf
    
    intCntTbl = 0
    
    For Each ws In wb.Worksheets
            If InStr(ws.Name, "Intro") Then
            ElseIf InStr(ws.Name, "Terms") Then
            Else
                intCntTbl = intCntTbl + 1
                    ws.Activate
                    ws.Cells.Select
                    ws.Cells.NumberFormat = xlText
                    intMaxCol = Excel.Cells.Find(What:="*", After:=[A1], _
                                            SearchOrder:=xlByColumns, _
                                            SearchDirection:=xlPrevious).Column
                    intMaxRow = Excel.Cells.Find(What:="*", After:=[A1], _
                                            SearchOrder:=xlByColumns, _
                                            SearchDirection:=xlPrevious).Row
                                            

                Set tdf = db.CreateTableDef(ws.Name)
                c = 1
                With tdf
CellLoop:
                    Do While Not (ws.Cells(1, c) = vbNullString)
                        .Fields.Append .CreateField(ws.Cells(1, c), dbText, 30)
                    c = c + 1
                    Loop
                    If ws.Cells(1, c) = vbNullString Then
                        .Fields.Append .CreateField("F" & c, dbText, 30)
                        c = c + 1
                        If ws.Cells(1, c) = vbNullString Then
                            .Fields.Append .CreateField("F" & c, dbText, 30)
                            c = c + 1
                            If ws.Cells(1, c) = vbNullString Then
                                .Fields.Append .CreateField("F" & c, dbText, 30)
                                c = c + 1
                                If ws.Cells(1, c) = vbNullString Then
                                Else
                                    GoTo CellLoop
                                End If
                            Else
                                GoTo CellLoop
                            End If
                        Else
                            GoTo CellLoop
                        End If
                    Else
                        GoTo CellLoop
                    End If
                   
                   z = c
                   For z = c To 50
                    .Fields.Append .CreateField("F" & z, dbText, 30)
                    Next z
                    
                    db.TableDefs.Append tdf
                    Set fld = Nothing
                    Set tdf = Nothing
                End With 'tdf
                    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ws.Name, strWbName, True, ws.Name & "$"
            
            End If 'ws.Name
    Next ws
    
    MsgBox intCntTbl & " worksheets imported!", vbInformation, "Import Complete"
    
ExitSub:
    Excel.Application.DisplayAlerts = False
    wb.Close
    Set wb = Nothing
    Excel.Application.DisplayAlerts = False
    db.Close
    Set db = Nothing
    
    Exit Sub
    
ErrHandle:
    ErrTalk (Forms!frmImport!txtImportID)
    Resume ExitSub
End Sub

And here are the data values that are causing the "ImportErrors":
[UL]
[LI]130A[/LI]
[LI]144-23[/LI]
[LI]71-37[/LI]
[LI]39 11[/LI]
[LI]116P[/LI]
[LI]380A[/LI]
[/UL]

Here is the import error description (same for all records):

Type Conversion Failure

Any thoughts/ideas as to how to get around these import errors?


--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

As an update on this issue, I have found yet something else that seems strange to me. That is I can go back and manually add in the values that were left out, and Access accepts it without question. Same values, same fields, just typed in instead of imported.

Right now, I'm thinking one crazy work around will be to setup VBA to go and copy/paste the specific values that are being left out.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

In case anyone is interested, I'm currently just working on some code to go and grap the data that is left out. In this process, I started looking more seriously as to how to make sure Excel is "gone" - no active processes for it by the time I'm finished. Before, I was having Excl apps still lingering out there (under "task manager" - "processes"). I found this code that I believe will take care of it - I tested it separately first:

http://www.vbforums.com/showthread.php?s=&threadid=297109

It is meant for an external VB app, but it seems to work quite well within Access VBA.

I only used this much of it in my test:

Private m_appExcel As Excel.Application
Private m_Book As Excel.Workbook
Private m_Sheet As Excel.Worksheet

Private Sub TestExcel()
    Set m_appExcel = New Excel.Application
    Set m_Book = m_appExcel.Workbooks.Add
    Set m_Sheet = m_Book.Sheets(1)
    
    m_appExcel.Visible = True
    
    m_appExcel.DisplayAlerts = False
    m_appExcel.Quit
    Set m_appExcel = Nothing
    Set m_Book = Nothing
    Set m_Sheet = Nothing
End Sub

Then I just stepped through it instead of an all-out run, so that I could actually see what is going on.

The Excel process comes available when the workbook object is instantiated, and then is finally killed after the worksheet object is set to "nothing".

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Another update. On the Excel instance won't close deal, I found sort of a solution/answer. Actually sort of figured it out by testing.

For some reason, sometimes Excel won't close it's process when told to do so from within Access VBA - even though the call to it has ended, it stays open.

Well, I tried just closing the whole Access database, and then the extra instance of Excel closed. So that will be a simple enough solution - all instances will close when the database is closed.

Or so it seems for now.

Still working on the main thing, though.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

To avoid ghost Excel instances always use full qualified object.
Replace this:
Set wb = Workbooks.Open(Forms!frmImport!txtXlFile)
with this:
Dim xl As Object: Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open(Forms!frmImport!txtXlFile)

and this:
intMaxCol = Excel.Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
with this:
intMaxCol = ws.Cells.Find(What:="*", After:=ws.Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

and so on ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Bingo! PHV, I think that the last part there is the only part I haven't fixed so far - I ended up instantiating Excel similarly to what you did, but I still had an instance. Thanks!

--

"If to err is human, then I must be some kind of human!" -Me