Error Handling Help in a For...Next Loop

[ShamuKris]

Hi,

I'm Kris, and was searching around on Error Handlers in a For...next loop, and I'm stuck. I don't know a lot about error handling and I hope you can help me here. I'm working in Access 2003 with VBA ( I know...stuck in the dark ages with Access here at work lol)

I know exactly what is causing the problem...but, I'm unsure what error handler to code to make it work. The logic behind the code is to open an Excel workbook based on a date, import a row of excel cells as a new record in Access, close that workbook, then go to the next date. But, some times the next day looped doesn't exist as an Excel File. So, I'm trying to tell the code to skip over that date since it doesn't exist, and go to the next date.

I have a form where the user enters a from date and to date (These vaules are stored variables "FromDay" and "ToDay". (i.e FromDay = 18 and ToDay = 25)

The XL files (not every day will have a file date)
Estore Refunds 4-19-2010
Estore Refunds 4-21-2010
Estore Refunds 4-22-2010

Pseudo code

For nDays = FromDay to ToDay Step 1
Set Global_XL_Estore_Records = Global_XL_Estore.Workbooks.Open("filepath" & "Estore Refund" & month "-" & ndays & "-" year & ".xls")

For 4 to 96 Step 1 ' each step is a row in the worksheet
"load XL cells in variables, add new record in access table
'close workbook
Next
'go to next workbook xl file
Next

My problem is that the object Global_XL_Estore_Records = nothing when nDays hits 18, or 20, and throws the error "file cannot be found...blah blah blah" I then either have to click end or debug...bringing me back to the code to fix

The code works great when lets say the user puts in 4/21/2010 to 4/22/2010 because those XL files exist at the file path. It's just when the user puts in a date range like 4/15/2010 to4/25/2010 where some files dates exist where other dates don't.

Any help or adivse will be great...as this is the best I can remember the code...since it's at work, and I can't bring home work

[ShamuKris]

Some how teh code hit me late tonight...

But, It looks like I got it to do what I wanted it to do.

[vba]
Private Sub import_button_Click()

Dim nDays As Integer
Dim FromDay As Integer
Dim ToDay As Integer

Dim No_Workbook_Boolean As Boolean

FromDay = FromDay_Textbox.Value
ToDay = ToDay_Textbox.Value

Dim db As Database
Dim rec As Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("Estore_Refunds_Table")

Dim XLApp As Object
Dim XLBook As Object

Set XLApp = Excel.Application
Set XLBook = Excel.Workbooks

For nDays = FromDay To ToDay

No_Workbook_Boolean = False

On Error GoTo No_XL_file
Set XLBook = Workbooks.Open("C:\Users\Kris\Desktop\" & "Estore REfunds 4-" & nDays & "-2010.xlsx")

If No_Workbook_Boolean = False Then
StatusListbox.AddItem "Estore REfunds 4-" & nDays & "-2010.xlsx found"
'load excel cells into variables, update access record with row cells
Else
StatusListbox.AddItem "Estore REfunds 4-" & nDays & "-2010.xlsx not found"
'give error, skip file, and go to next workbook
End If

No_XL_file:
No_Workbook_Boolean = True
Resume Next
Next
End Sub
[/vba]

[CreganTur]

Traditionally the On Error GoTo Error_Handler line is entered just after your variable declaration statement.

The main issue I see with your error handling routine is that you have set it so that every possible error will run your No_XL_File routine. You can easily amke your error handling more robust by finding out the error number that occurs when you are missing the XL file. Then you can use a SELECT CASE statement like this:
[VBA]Error_Handler:
Select Case err.Number
case (error number for XL file missing)
No_Workbook_Boolean = True
Resume Next
case else
msgbox "Error " & err.number & ": " & err.message
exit sub
end sub
[/VBA]

In this example you are handling your known error by allowing the code to resume, but any other error will kick off an error message telling you what the problem is and then stopping the run.

HTH

[c_smithwick]

Rather than throw an error in the first place, check for existance of the file with a Dir Function. If it's found then execute the record retreival, otherwise display your "not found" message. If you know something is likely to happen and write code to handle it, it is much better than trapping errors. When you find that something is causing an error, rather than handle the error, write code to prevent it. Figuring out what to do to prevent the error will make you a better programmer in the long run.