Error Handling 3421 Codes

[DeleteZx4Iwanttodelete]

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.

'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

 

[static]

If you are using a temp table to import data to, set each field to text. You wont get any datatype errors and you can validate the types before copying them to the master table.

 

[DeleteZx4Iwanttodelete]

While this would work, it doesn't work for this process. I need to reject the files that don't import for manual inspection. Moving data to a temp table in your idea doesn't allow me to have full audit control of each individual file that fails. What I want to do is encounter the file. Move to an error process. Code would then move file to a reject folder and alert the user.

 

[static]

According to what you said and what your code says, you are adding the data to a temp table before moving it to the master table.

So what's the problem?

 

[DeleteZx4Iwanttodelete]

The issue is I want to catch the issue at the individual file level before any data is written to a temp or permanent table. I need to review each individual file should it reject. So. Writing data to a temp table and THEN looking for issues creates more opportunities for confusion and miss alignment

 

[The_Doc_Man]

You have eleven fields, a couple of which are synthesized. Before you do the insert in question, run formatting tests.

For things that should be a number, there is the IsNumeric function, which returns TRUE if the input is a number or COULD be a number. For example both of these are TRUE:

IsNumeric( 234 )
IsNumeric( "234" )

The way I see it, you can write fewer than a dozen tests and validate whether you are getting back viable data. Fewer, because some of your fields are alphanumeric or simple alphabetic anyway. Then if everything passes the screening tests, the numerics can be easily converted by doing either CLng() or CInt() depending on expected numeric range.

The only oddball is your date field, but not to worry - IsDate() also exists and is TRUE or FALSE based on the validity of the string interpreted as a date.

Just remember that any date string is valid if the translated date in question is greater than 1/1/100 (yes, I meant to go that far back) and less than 31-Dec-9999.

I wouldn't bother to add any records at all until I did the screening tests. If you want to do more screening, remember the functions IsNull and IsEmpty (meaning empty string).

 

[isladogs]

Cross posted at http://www.accessforums.net/showthread.php?t=66476

 

[Galaxiom]

The only oddball is your date field, but not to worry - IsDate() also exists and is TRUE or FALSE based on the validity of the string interpreted as a date.

Just remember that any date string is valid if the translated date in question is greater than 1/1/100 (yes, I meant to go that far back) and less than 31-Dec-9999.

.... along with any date that is valid in out of region formats. Regardless of regional settings, date functions pass, at least, US(mm/dd/yy), UK(dd/mm/yy) and ISOish (yy-mm-dd or yy/mm/dd).

So even an impossible UK/US date like 29/2/14 will be quietly accepted as ISO and converted to 14 Feb 1929. They just default to the regional setting.

This isn't Access or even Office that is so stupid as to ignore the regional setting. It is the Windows API they are using. I hope there is a special place in hell for whoever made that decision, where they spend eternity manually correcting invalid dates.

 

[The_Doc_Man]

Oh, no, Galaxiom... their punishment is that they must stay in Hell until Windows is hacker-proof.

 

[DeleteZx4Iwanttodelete]

Doc_Man,
Thanks, ill use this approach. Doesn't answer why the code is not automatically moving to the Error section when a data-type issue occurs, but this allows for a solution that will move everything forward.

Thanks

 

[The_Doc_Man]

Doesn't answer why the code is not automatically moving to the Error section when a data-type issue occurs,

I have an answer for that, too, maybe.

Error_Handling:
    
    rst.Close
    ExcelApp.DisplayAlerts = False
    ExcelApp.Quit
    ExcelApp.DisplayAlerts = True
    Pause (1.5)  
    Call fso.MoveFile(strPath, strFailedPath)
    strFailCount = strFailCount + 1
    [COLOR="Red"]GoTo On_Error_Resume[/COLOR]

The highlighted line is incorrect syntax. In an error handler, you are NOT in a position to do a GOTO. You are in an interrupt routine, technically not even in the context of your subroutine at that precise moment.

In brief, the ON ERROR reference identifies a special context called a TRAP HANDLER which is a special type of subroutine called as the result of a hardware or software interrupt. This differs from subroutines called via CALL (or in some versions of BASIC, the GOSUB verb.)

In interrupt context, you can see your variables but your code is in a unique situation. No other code is running in your process - or CAN run in your process - until you safely exit the context via the RESUME keyword, which is simultaneously TWO commands:

(a) release trap context and
(b) goto the target of the RESUME.

Replace your GOTO with RESUME in that case and you might get more consistent behavior.

Now, one last comment. Instead of displaying stuff and pausing for a set amount of time, either accumulate stuff in a summary to be displayed at the end of the loop OR put a "failure occurred" message box with the vbOKOnly option when the trap occurs. The strategy of having a flash of an error for 1.5 seconds is likely to be frustrating to anyone watching it since they can't do anything about it. My suggested strategy is an all-or-nothing-at-all approach. Either don't bother to show the flashing message or stop hard when it occurs and wait for acknowledgement via the OK in the message box.

 

[DeleteZx4Iwanttodelete]

Thanks for the extra info.

Your response is on point, an makes sense. In the scope of this project, a full on "RESUME" doesnt work as the file needs to be "REJECTED" and moved to a manual review location. This is why the intent of encountering an "ERROR" it is expected to close the record-set, quit EXCEL, and move onward to the next file in the directory.

Additionally, the PAUSE is there only to allow Excel to fully close on slower machines. 1.5 Seconds is prolly too long, and could be shortened.

To move forward, i used the IsNumeric, and this is now capturing bad files before hand, almost rendering the Error Handle pointless for this limited scope project. Thanks for all the info!

 

[Frothingslosh]

I don't see the issue. You're closing excel, running a move procedure, and then you want to continue.

Do as Doc said and replace GoTo with Resume and you're all set. You can, after all, use a resume to go to any label and not just the line after the error.