Why do I get this error?

Lopez, Ahiezer 236

Im not sure why I have this code error when trying to import an excel file. Please help.

User's image

File Type User's image

Tanay Prasad 2,115 Reputation points

2023-07-20T05:54:21.9466667+00:00

Make sure that your Excel file is not open or in use by another application when importing.

Can you please paste your code?

Lopez, Ahiezer 236

Here is the code.

Private Sub GetUmFile()
'Get File
    Dim wb As Excel.Workbook
    Dim xlApp As Excel.Application
    Dim db As DAO.Database
    DoCmd.SetWarnings False
    If fTableExists("tblHTSAudit") Then
        DoCmd.RunSQL "Delete From tblHTSAudit"
    End If
    DoCmd.SetWarnings True
    If SheetList.Value = "" Then
        MsgBox "WorkSheet not selected"
        Exit Sub
    End If
Ender:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "tblHTSAudit", ImpFile.Value, True, SheetList.Value & "!"
    strSQL = "DELETE tblHTSAudit.[ITEM NO]" _
        & vbCr & "FROM tblHTSAudit" _
        & vbCr & "WHERE (((tblHTSAudit.[ITEM NO]) Is Null));"
        
        
   Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select * From tblHTSAudit")
    If Not rs.EOF And Not rs.BOF Then
        rs.MoveFirst
        
        While (Not rs.EOF)
'            Debug.Print rs.Fields("BUSINESS UNIT")
            
            If rs.Fields("BUSINESS UNIT") <> "Hussmann" And rs.Fields("BUSINESS UNIT") <> "Commercial Insulating Glass" And rs.Fields("BUSINESS UNIT") <> "Commercial Refrigerator Door" Then
            MsgBox "The following data record has an error in the BUSINESS UNIT field. This error needs to be fixed on the report that was used to update." & vbNewLine & vbNewLine & _
            "ITEM NO: " & rs.Fields("ITEM NO") & vbNewLine & _
            "DESCRIPTION: " & rs.Fields("DESCRIPTION - AS RECD - ENGLISH LANGUAGE") & vbNewLine & "HTSUS: " & rs.Fields("HTSUS") & _
            vbNewLine & "BUSINESS UNIT: " & rs.Fields("BUSINESS UNIT"), vbCritical, "BUSINESS UNIT ERROR FOUND"
            
            sql = "DELETE tblHTSAudit.* FROM tblHTSAudit WHERE (([tblHTSAudit].[ITEM NO]) = '" & rs.Fields("ITEM NO") & "') and (([tblHTSAudit].[BUSINESS UNIT]) = '" & rs.Fields("BUSINESS UNIT") & _
            "')"

Debug.Print sql

DoCmd.SetWarnings False

DoCmd.RunSQL sql
DoCmd.SetWarnings True
GoTo line:
            
            
            End If
'            On Error GoTo line
            If IsNull(DLookup("HTSUS", "_USHTS", "HTSUS = '" & rs.Fields("HTSUS") & "'")) Then
                MsgBox "The following data record has an error in the HTSUS field. This error needs to be fixed on the report that was used to update." & vbNewLine & vbNewLine & _
            "ITEM NO: " & rs.Fields("ITEM NO") & vbNewLine & _
            "DESCRIPTION: " & rs.Fields("DESCRIPTION - AS RECD - ENGLISH LANGUAGE") & vbNewLine & "HTSUS: " & rs.Fields("HTSUS") & _
            vbNewLine & "BUSINESS UNIT: " & rs.Fields("BUSINESS UNIT"), vbCritical, "HTSUS ERROR FOUND"
            
            sql = "DELETE tblHTSAudit.* FROM tblHTSAudit WHERE (([tblHTSAudit].[ITEM NO]) = '" & rs.Fields("ITEM NO") & "') and (([tblHTSAudit].[BUSINESS UNIT]) = '" & rs.Fields("BUSINESS UNIT") & _
            "')"

            Debug.Print sql
            
            DoCmd.SetWarnings False
            
            DoCmd.RunSQL sql
            DoCmd.SetWarnings True
GoTo line:
            
            'if error about record being deleted go onto next record
            

            End If




            If IsNull(DLookup("ECCN", "dbo_ECCN", "ECCN = '" & rs.Fields("ECCN") & "'")) Then
                MsgBox "The following data record has an error in the ECCN field. This error needs to be fixed on the report that was used to update." & vbNewLine & vbNewLine & _
            "ITEM NO: " & rs.Fields("ITEM NO") & vbNewLine & _
            "DESCRIPTION: " & rs.Fields("DESCRIPTION - AS RECD - ENGLISH LANGUAGE") & vbNewLine & "HTSUS: " & rs.Fields("HTSUS") & _
            vbNewLine & "BUSINESS UNIT: " & rs.Fields("BUSINESS UNIT"), vbCritical, "ECCN ERROR FOUND"
            
            sql = "DELETE tblHTSAudit.* FROM tblHTSAudit WHERE (([tblHTSAudit].[ITEM NO]) = '" & rs.Fields("ITEM NO") & "') and (([tblHTSAudit].[BUSINESS UNIT]) = '" & rs.Fields("BUSINESS UNIT") & _
            "')"

            Debug.Print sql
            
            DoCmd.SetWarnings False
            
            DoCmd.RunSQL sql
            DoCmd.SetWarnings True
    GoTo line:
            
            'if error about record being deleted go onto next record
            

            End If
line:
            rs.MoveNext
        
        Wend
    
    End If
    rs.Close



    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "Done"
End Sub

Tanay Prasad 2,115 Reputation points

2023-08-02T07:04:18.1666667+00:00

Refer to the answer below.

1 answer

Tanay Prasad 2,115

Try running this code-

Private Sub GetUmFile()
    ' ... Your existing code ...

    strSQL = "DELETE FROM tblHTSAudit WHERE [ITEM NO] Is Null;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    ' ... Your existing code ...
    
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Select * From tblHTSAudit")
    If Not rs.EOF And Not rs.BOF Then
        rs.MoveFirst
        
        While (Not rs.EOF)
            ' ... Your existing code ...

            If rs.Fields("BUSINESS UNIT") <> "Hussmann" And rs.Fields("BUSINESS UNIT") <> "Commercial Insulating Glass" And rs.Fields("BUSINESS UNIT") <> "Commercial Refrigerator Door" Then
                ' ... Your existing code ...
                DoCmd.SetWarnings False
                DoCmd.RunSQL sql
                DoCmd.SetWarnings True
            End If
            
            ' ... Your existing code ...

            If IsNull(DLookup("HTSUS", "_USHTS", "HTSUS = '" & rs.Fields("HTSUS") & "'")) Then
                ' ... Your existing code ...
                DoCmd.SetWarnings False
                DoCmd.RunSQL sql
                DoCmd.SetWarnings True
            End If

            ' ... Your existing code ...

            If IsNull(DLookup("ECCN", "dbo_ECCN", "ECCN = '" & rs.Fields("ECCN") & "'")) Then
                ' ... Your existing code ...
                DoCmd.SetWarnings False
                DoCmd.RunSQL sql
                DoCmd.SetWarnings True
            End If

            ' ... Your existing code ...
            rs.MoveNext
        Wend
    End If
    rs.Close

    ' ... Your existing code ...
    MsgBox "Done"
End Sub

Lopez, Ahiezer 236 Reputation points

2023-08-02T14:28:01.9066667+00:00

I am not sure how to implement the code.
Lopez, Ahiezer 236 Reputation points

2023-08-02T18:30:35.8233333+00:00

What about the importing the data part of the code?

Share via

Why do I get this error?

1 answer