ShamuKris
04-23-2010, 06:42 PM
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 :(
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 :(