Visual Basic error

[ebaltas]

I'm trying to import a series of excel spreadsheets while also running some queries to delete any uneeded rows in the spreadsheets. I can't see to to get the following code to properly work. Any help someone can provide me would be greatly appreciated:
Private Sub Command5_Click()
Dim svy_impt_path
Dim filename
Dim counter As Integer

svy_impt_path = InputBox("Enter the path in which the survey results files are" & _
" located." & Chr(13) & Chr(13) & "Example: c:\abc\surveys\", "Import Survey Results")

If svy_impt_path = "" Then
MsgBox "Operation Canceled."
Exit Sub
End If

DoCmd.SetWarnings False

DoCmd.OpenQuery "Clear Imported Survey Results Tbl"
DoCmd.OpenQuery "Clear Location and Customer Svy Tbl"

filename = Dir(svy_impt_path & "*.xl*") ' Retrieve the first entry.
Do While filename <> "" ' Start the loop.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Imported Survey Results", _
svy_impt_path & filename, True, "'Activity Survey'!d2:g"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Location and Customer Survey Results", _
svy_impt_path & filename, True, "'Location and Customer Survey'!b2:e"

filename = Dir
Loop

DoCmd.OpenQuery "Remove Rows in Act Svy that do not have percentages"
DoCmd.OpenQuery "Remove Rows in Act Svy that do not have acts"
DoCmd.OpenQuery "Remove Rows in L&C Svy that do not have percentages"
DoCmd.OpenQuery "Remove Rows in L&C Svy that do not have loc"

DoCmd.OpenQuery "Clear Imported_Survey_Results Tbl"
DoCmd.OpenQuery "Apd I S R to I_S_R tbl"
DoCmd.OpenQuery "Change 8s to 8-1"

DoCmd.OpenQuery "Clear Cost Object_Svy_Results Tbl"
DoCmd.OpenQuery "Apd L&C to C Obj_Svy_Res Tbl"


'Delete Import Error Tables

On Error GoTo ErrorHandler

DoCmd.DeleteObject acTable, "e_ImportErrors"
DoCmd.DeleteObject acTable, "g_ImportErrors"

For counter = 1 To 100
DoCmd.DeleteObject acTable, "e_ImportErrors" & counter
DoCmd.DeleteObject acTable, "g_ImportErrors" & counter
Next

ErrorHandler:

Select Case Err.Number
Case 3011
Resume Next
End Select

DoCmd.SetWarnings True

MsgBox "Survey results have been imported and compiled.", , "Model Message"

End Sub

[coolbiz]

what exactly the error message that you're getting?

-Cool Bizs

[ebaltas]

When I run this code, it says the spreadsheets have been sucessfully import and compiled. The problem is that the table where they are supposed to be loaded into is still blank. Basically the code says that it ran but none of the results that I need are being uploaded into the table. Any help you can provide is greatly appreciated

[coolbiz]

Well the code seems to be okay but of course there are a lot more to look at. First of all you might wanna NOT .SetWarnings FALSE so that if there is any error, it will be prompted for you. Secondly, your error trapping can be improved to display error message if it occurs. Thirdly, your last message should only display Successful if it does not get to the error handler. Lastly, you might wanna start debugging each line of your code. Step through each line and see the result. It might not be doing what you wanted it to do.

I've modified the code a little so that it traps the error better and better exit message.

private Sub Command5_Click()
Dim svy_impt_path
Dim filename
Dim counter as Integer

svy_impt_path = InputBox("Enter the path in which the survey results files are" & _
" located." & Chr(13) & Chr(13) & "Example: c:\abc\surveys\", "Import Survey Results")

If svy_impt_path = "" then
MsgBox "Operation Canceled."
Exit Sub
End If

' set this to true first
DoCmd.SetWarnings true

DoCmd.OpenQuery "Clear Imported Survey Results Tbl"
DoCmd.OpenQuery "Clear Location and Customer Svy Tbl"

filename = Dir(svy_impt_path & "*.xl*") ' Retrieve the first entry.
Do While filename <> "" ' Start the loop.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Imported Survey Results", _
svy_impt_path & filename, true, "'Activity Survey'!d2:g"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "Location and Customer Survey Results", _
svy_impt_path & filename, true, "'Location and Customer Survey'!b2:e"

filename = Dir
Loop

DoCmd.OpenQuery "Remove Rows in Act Svy that do not have percentages"
DoCmd.OpenQuery "Remove Rows in Act Svy that do not have acts"
DoCmd.OpenQuery "Remove Rows in L&C Svy that do not have percentages"
DoCmd.OpenQuery "Remove Rows in L&C Svy that do not have loc"

DoCmd.OpenQuery "Clear Imported_Survey_Results Tbl"
DoCmd.OpenQuery "Apd I S R to I_S_R tbl"
DoCmd.OpenQuery "Change 8s to 8-1"

DoCmd.OpenQuery "Clear Cost Object_Svy_Results Tbl"
DoCmd.OpenQuery "Apd L&C to C Obj_Svy_Res Tbl"

'Delete Import error Tables
on error GoTo ErrorHandler

DoCmd.DeleteObject acTable, "e_ImportErrors"
DoCmd.DeleteObject acTable, "g_ImportErrors"

for counter = 1 to 100
DoCmd.DeleteObject acTable, "e_ImportErrors" & counter
DoCmd.DeleteObject acTable, "g_ImportErrors" & counter
next

' successful message box
DoCmd.SetWarnings true

MsgBox "Survey results have been imported and compiled.", , "Model Message"
Exit Sub

ErrorHandler:

Select Case Err.Number
Case 3011
resume next
Case else
Msgbox "error occurred: " & Err.Number & " - " & Err.Description
End Select

End Sub




Hope this helps,
-Cool Bizs