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.TransferS preadsheet 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 transferspreads heets. 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.TransferS preadsheet acImport, 8, "Table 1", Me.tb_FileName, 1, "Test1"

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

DoCmd.TransferS preadsheet 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 TransferSpreads heet() 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.