Hiding error messages for DoCmd.TransferSpreadsheet

[jerelp]

I have a form with one button the code for that button is

On Error Resume Next
DoCmd.TransferSpreadsheet acImport, 8, "FuelConsumption NewData", Me.tb_FileName, 1, "sheet2$"
 
On Error Resume Next
DoCmd.TransferSpreadsheet acImport, 8, "Inbound NewData", Me.tb_FileName, 1, "Inbound Trains$"
DoCmd.TransferSpreadsheet acImport, 8, "Outbound NewData", Me.tb_FileName, 1, "Outbound Trains$"
 
On Error Resume Next
DoCmd.TransferSpreadsheet acImport, 8, "LPMH NEWDATA", Me.tb_FileName, 1 ', "March 09$"
 
MsgBox "Import Complete"

Me.tb_FileName

This is a text box that contains the location of the file being imported

I am aware that "On Error Resume Next" is the improper way to deal with error handling but I haven't been able to find a solution to fix the problem I have

The Problem is that, in case that a spread sheet doesn't match any of the 4 or doesn't meet the format then show a error message that states "Import file is not in the correct format." but I don't want to show a error message for any of the DoCmd.TransferSpreadsheet and no matter what there will be an error message for them because I can only import one spreadsheet at a time and they are located on 3 seperate spreadsheets/workbooks.

The code that i've attempted to fix the problem is this

'On Error GoTo Err1
 '   DoCmd.TransferSpreadsheet acImport, 8, "FuelConsumption NewData", Me.tb_FileName, 1, "sheet2$"
  '  MsgBox "FuelConsumption Import Complete"
 
    'On Error Resume Next
 
'Err1:
    'On Error GoTo Err2:
    'DoCmd.TransferSpreadsheet acImport, 8, "Inbound NewData", Me.tb_FileName, 1, "Inbound Trains$"
    'DoCmd.TransferSpreadsheet acImport, 8, "Outbound NewData", Me.tb_FileName, 1, "Outbound Trains$"
    'MsgBox "Inbound/Outbound Import Complete"
 
    'On Error Resume Next
 
'Err2:
    'On Error GoTo Err3:
    'DoCmd.TransferSpreadsheet acImport, 8, "LPMH NEWDATA", Me.tb_FileName, 1 ', "March 09$"
    'MsgBox "LPMH Import Complete"
 
    'On Error Resume Next
 
'Err3:
 
    'MsgBox "The Import data selected is not in the correct format"
 
    'Exit Sub

Using this code no matter what, I get an error for atleast one of the transferspreadsheets. It would pass one then import then error or not import any data and just error.

Run-Time Error '3125'

Please help!
Thanks.

[ADezii]

I'm a little confused as to what exactly you are asking but I think that

On Error GoTo 0

may help. This line effectively disables Error Handling in the Procedure in which it is invoked.

[jerelp]

Where would i place that statement?

Hopefully this explanation will help.

I have one text box to one location of one excel spreadsheet
I have one button and that button gets the location of the file from the text box but it has to get 3 different locations in order to import all the data.

Example:
to Import file "test2.xls"
for table 2

it goes through
DoCmd.TransferSpreadsheet acImport, 8, "Table 1", Me.tb_FileName, 1, "Test1"

DoCmd.TransferSpreadsheet acImport, 8, "Table 2", Me.tb_FileName, 1, "Test2"

DoCmd.TransferSpreadsheet acImport, 8, "Table 3", Me.tb_FileName, 1, "Test3"


So in order to get to import "test2.xls" it erros at Test1 then imports through Test2 and errors at Test3.

I don't want it to error for neither of them but, I do want it to error if it erros for ALL of them (if the spreadsheet being imported doesn't meet the criteria for Test1, Test2,Test3).

[ADezii]

This is a rather unusual request, but assuming I now understand your dilemma correctly, the following code will generate an Error Message only under the condition that ALL THREE TransferSpreadsheet() actions produce an Error. If only 1, None, or 2 fail, the Error Message will not appear.

Dim intNumOfErrors As Integer
On Error Resume Next
 
intNumOfErrors = 0      'Initialize
 
DoCmd.TransferSpreadsheet acImport, 8, "Table 1", Me.tb_FileName, 1, "Test1"
If Err.Number <> 0 Then     'Error!
  intNumOfErrors = intNumOfErrors + 1
    Err.Clear       'Clear the Error Object
End If
 
DoCmd.TransferSpreadsheet acImport, 8, "Table 2", Me.tb_FileName, 1, "Test2"
If Err.Number <> 0 Then     'Error!
  intNumOfErrors = intNumOfErrors + 1
    Err.Clear       'Clear the Error Object
End If
 
DoCmd.TransferSpreadsheet acImport, 8, "Table 3", Me.tb_FileName, 1, "Test3"
If Err.Number <> 0 Then     'Error!
  intNumOfErrors = intNumOfErrors + 1
    Err.Clear       'Clear the Error Object
End If
 
If intNumOfErrors = 3 Then
  MsgBox "All 3 Imports Failed!", vbCritical, "ALL Imports Failure"
End If

[jerelp]

Thank you it worked perfectly

[ADezii]

Glad it worked out for you.