Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    31

    Unanswered: Error Handling 3421 Codes

    Hello,

    I built a db recently with the goal of processing through a folder of excel forms. There was a manual effort going on where someone was taking 10's of files daily, opening each up to capture a couple of key items from the form.

    I created a db that would allow someone to point the data base to an "import" folder, "completed" folder, and "failed" folder.

    The goal was to process all the excel files from the import folder, open each file in the background, grab the key fields and write to a data set into a table, then append that import to the master data table.

    Each file processed successfully would be move to a completed location. Any Failed imports (due to bad file, or data issues) would be skipped and moved to the failed folder.

    Everything works fine.....except...

    If a file has text for example where a number should be,i get a data type conversion error 3421.

    I have built in Error Handle in the code, but 3421, does not appear to trigger this affect.

    What do i need to do to allow my code to encounter this error, and skip the file to move onward with the code?

    Here is a snapshot of the key aspect of the code.


    Code:
    'Open Excel in Background and pull data
            Set ExcelApp = CreateObject("Excel.Application")
            Dim WkBk As Excel.Workbook
            Set WkBk = ExcelApp.Workbooks.Open(strPath)
            Dim rst As Recordset
            Set rst = CurrentDb.OpenRecordset("Auto_Load ")
                
            On Error GoTo Error_Handling
            
                'Records data from Excel file and saves to Load Table
                With rst
                
                    .AddNew
                
                    .Fields("Name") = WkBk.Sheets(1).Range("D7")
                    .Fields("Company_ID") = WkBk.Sheets(1).Range("D8")
                    .Fields("Pro") = WkBk.Sheets(1).Range("D9")
                    .Fields("Date") = WkBk.Sheets(1).Range("D10")
                    .Fields("Provider") = WkBk.Sheets(1).Range("D11")
                    .Fields("Support_Contact_Name") = WkBk.Sheets(1).Range("J8")
                    .Fields("Support_Contact_Email") = WkBk.Sheets(1).Range("J9")
                    .Fields("Support_Contact_Phone") = WkBk.Sheets(1).Range("J10")
                    .Fields("Total_Users") = WkBk.Sheets(1).Range("L23")
                    .Fields("EID") = strUserID & "AutoLoader"
                    .Fields("File_Name") = strCount & "_" & strNow & "_" & strFile
                .Update
                End With
            'Quit Excel
            ExcelApp.DisplayAlerts = False
            ExcelApp.Quit
            ExcelApp.DisplayAlerts = True
            Pause (1.5)
            'Move processed file to completed director
            Call fso.MoveFile(strPath, strComplete)
            strCount = strCount + 1
    
    On_Error_Resume:
               
    
    'Loop to next file
    strFile = Dir$ 'Next file
    Loop
    
    'Append Load table to main table
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Auto_Load_Append"
    DoCmd.SetWarnings True
    
    'Output actions for review
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Auto_Load", "C:\Users\" & strUserID & "\Desktop\AUTO_IMPORT_REVIEW_" & strNow & ".xls", True
    
    MsgBox "Load Complete! There were " & strCount & " Successful files loaded and " & strFailCount & " files that failed." & vbCrLf & "Please review the output file on your desktop and/or the Failed folder."
     
    Exit Sub
    
    Error_Handling:
        
        rst.Close
        ExcelApp.DisplayAlerts = False
        ExcelApp.Quit
        ExcelApp.DisplayAlerts = True
        Pause (1.5)  
        Call fso.MoveFile(strPath, strFailedPath)
        strFailCount = strFailCount + 1
        GoTo On_Error_Resume
    
    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    804
    Provided Answers: 2
    Hi

    You need to validate the data in the worksheet before trying to assign it to the recordset.

    Normally the IsNumeric() function does the trick for this particular error.

    What you do if it is not numeric is not clear with the program structure you posted hear. I assume this would be a fail?

    For instance you could check all the data first in a separate (Boolean) Function (there maybe other data type you need to check?) and if it returns False (ie failed) then skip the rest of loop and move it to the failed folder. Just a thought.

    HTH


    MTB

  3. #3
    Join Date
    May 2009
    Posts
    31
    Thanks....this helps. Going to try to intergrate that into the solution.

    I'm just still confused as to why the run time box pops up despite the "error handling" that is coded. BUt this approach would work as well

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •