By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,010 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

Hiding error messages for DoCmd.TransferSpreadsheet

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

Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. DoCmd.TransferSpreadsheet acImport, 8, "FuelConsumption NewData", Me.tb_FileName, 1, "sheet2$"
  3.  
  4. On Error Resume Next
  5. DoCmd.TransferSpreadsheet acImport, 8, "Inbound NewData", Me.tb_FileName, 1, "Inbound Trains$"
  6. DoCmd.TransferSpreadsheet acImport, 8, "Outbound NewData", Me.tb_FileName, 1, "Outbound Trains$"
  7.  
  8. On Error Resume Next
  9. DoCmd.TransferSpreadsheet acImport, 8, "LPMH NEWDATA", Me.tb_FileName, 1 ', "March 09$"
  10.  
  11. MsgBox "Import Complete"
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 'On Error GoTo Err1
  2.  '   DoCmd.TransferSpreadsheet acImport, 8, "FuelConsumption NewData", Me.tb_FileName, 1, "sheet2$"
  3.   '  MsgBox "FuelConsumption Import Complete"
  4.  
  5.     'On Error Resume Next
  6.  
  7. 'Err1:
  8.     'On Error GoTo Err2:
  9.     'DoCmd.TransferSpreadsheet acImport, 8, "Inbound NewData", Me.tb_FileName, 1, "Inbound Trains$"
  10.     'DoCmd.TransferSpreadsheet acImport, 8, "Outbound NewData", Me.tb_FileName, 1, "Outbound Trains$"
  11.     'MsgBox "Inbound/Outbound Import Complete"
  12.  
  13.     'On Error Resume Next
  14.  
  15. 'Err2:
  16.     'On Error GoTo Err3:
  17.     'DoCmd.TransferSpreadsheet acImport, 8, "LPMH NEWDATA", Me.tb_FileName, 1 ', "March 09$"
  18.     'MsgBox "LPMH Import Complete"
  19.  
  20.     'On Error Resume Next
  21.  
  22. 'Err3:
  23.  
  24.     'MsgBox "The Import data selected is not in the correct format"
  25.  
  26.     '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.
Mar 16 '10 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. Dim intNumOfErrors As Integer
  2. On Error Resume Next
  3.  
  4. intNumOfErrors = 0      'Initialize
  5.  
  6. DoCmd.TransferSpreadsheet acImport, 8, "Table 1", Me.tb_FileName, 1, "Test1"
  7. If Err.Number <> 0 Then     'Error!
  8.   intNumOfErrors = intNumOfErrors + 1
  9.     Err.Clear       'Clear the Error Object
  10. End If
  11.  
  12. DoCmd.TransferSpreadsheet acImport, 8, "Table 2", Me.tb_FileName, 1, "Test2"
  13. If Err.Number <> 0 Then     'Error!
  14.   intNumOfErrors = intNumOfErrors + 1
  15.     Err.Clear       'Clear the Error Object
  16. End If
  17.  
  18. DoCmd.TransferSpreadsheet acImport, 8, "Table 3", Me.tb_FileName, 1, "Test3"
  19. If Err.Number <> 0 Then     'Error!
  20.   intNumOfErrors = intNumOfErrors + 1
  21.     Err.Clear       'Clear the Error Object
  22. End If
  23.  
  24. If intNumOfErrors = 3 Then
  25.   MsgBox "All 3 Imports Failed!", vbCritical, "ALL Imports Failure"
  26. End If

Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,638
I'm a little confused as to what exactly you are asking but I think that
Expand|Select|Wrap|Line Numbers
  1. On Error GoTo 0
may help. This line effectively disables Error Handling in the Procedure in which it is invoked.
Mar 16 '10 #2

P: 16
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).
Mar 16 '10 #3

ADezii
Expert 5K+
P: 8,638
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.
Expand|Select|Wrap|Line Numbers
  1. Dim intNumOfErrors As Integer
  2. On Error Resume Next
  3.  
  4. intNumOfErrors = 0      'Initialize
  5.  
  6. DoCmd.TransferSpreadsheet acImport, 8, "Table 1", Me.tb_FileName, 1, "Test1"
  7. If Err.Number <> 0 Then     'Error!
  8.   intNumOfErrors = intNumOfErrors + 1
  9.     Err.Clear       'Clear the Error Object
  10. End If
  11.  
  12. DoCmd.TransferSpreadsheet acImport, 8, "Table 2", Me.tb_FileName, 1, "Test2"
  13. If Err.Number <> 0 Then     'Error!
  14.   intNumOfErrors = intNumOfErrors + 1
  15.     Err.Clear       'Clear the Error Object
  16. End If
  17.  
  18. DoCmd.TransferSpreadsheet acImport, 8, "Table 3", Me.tb_FileName, 1, "Test3"
  19. If Err.Number <> 0 Then     'Error!
  20.   intNumOfErrors = intNumOfErrors + 1
  21.     Err.Clear       'Clear the Error Object
  22. End If
  23.  
  24. If intNumOfErrors = 3 Then
  25.   MsgBox "All 3 Imports Failed!", vbCritical, "ALL Imports Failure"
  26. End If
Mar 16 '10 #4

P: 16
Thank you it worked perfectly
Mar 17 '10 #5

ADezii
Expert 5K+
P: 8,638
Glad it worked out for you.
Mar 17 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.