Results 1 to 6 of 6
  1. #1
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202

    VBA to run msgbox failed import excel worksheet

    Good afternoon,
    I have written a code to import a specific excel worksheet using a picker. It works great. If the end user picks the wrong excel workbook that contains the worksheet, it gives the default error message and asks the end user to debug. I would like to avoid the end user seeing the default error message box, create my own and have it stop the code. I have tried multiple codes with much failure. Hoping for some insight below you will see my current code.

    Private Sub cmdBRP_Click()
    Dim dbs As DAO.Database
    Dim SelectedFile As String
    Dim FilePicker As FileDialog
    Dim SQLdelete As String
    Set dbs = CurrentDb
    Dim strXls As String


    Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
    FilePicker.AllowMultiSelect = False
    FilePicker.Filters.Add "Excel", "*.xls*", 1
    FilePicker.InitialFileName = "C:\Users"
    FilePicker.Title = "Please Select the Excel Data..."
    FilePicker.Show

    If FilePicker.SelectedItems.Count <> 0 Then

    'Delete table records
    CurrentDb.Execute "DELETE * FROM tblImportFM_BRP", dbFailOnError
    CurrentDb.Execute "DELETE * FROM tblAppImportFM_BRP", dbFailOnError

    'Select one file
    SelectedFile = FilePicker.SelectedItems(1)

    'Transfer Information
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImportFM_BRP", SelectedFile, True, "CC BRP!"


    'Open Tables for review
    DoCmd.OpenTable "tblImportFM_BRP", acViewNormal, acEdit '

    'Execute Append Query (converting info from import and updating into appended table)
    dbs.Execute "appqryImportFM_BRP", dbFailOnError

    'Message when successful
    MsgBox ("The data has been successfully loaded")

    'Message if no file was selected
    Else


    Call MsgBox("No file was selected.")
    End If
    End Sub

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Add an error trapping routine. Trap the error (the error number that you didn't report and handle any others that you have not foreseen). Where your code raises the error is not clear and is always helpful info. It looks like you're deleting records after the file is chosen and before the error is raised, but again, not clear.
    BTW, please use code tags (# on forum post toolbar) with proper indentation when posting code.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Thank you for your response. I am a beginner in coding and am unfamiliar with error trapping. To clarify some of your confusion as I was not clear. My thought process is to:
    1. Chose a excel worksheet to import using the picker
    2. Delete the old data in the tables
    3. Import new information into the cleared table as raw data
    THIS IS WHERE I GET THE ERROR CODE WHEN THE WRONG WORKSHEET/WORKBOOK IS CHOSEN. I WOULD LIKE TO STOP THE SYSTEM FROM AUTO GENERATING THE DEBUGING MESSAGE AND PUT MY OWN TO ALSO INCLUDE STOPPING THE REST OF THE CODE.
    3. Open the tables for review - there could be 500,000 records.
    4. Update and Append the tables
    5. Message complete or incomplete

    I hope this is clear. Thanks for the info on the code tags. Never knew they were there. Always just copied and pasted and always auto corrects without the indents. I am going to attempt to post the code again with the code tags.

    Code:
    Private Sub cmdBRP_Click()Dim dbs As DAO.Database
    Dim SelectedFile    As String
    Dim FilePicker      As FileDialog
    Dim SQLdelete       As String
    Set dbs = CurrentDb
    Dim strXls As String
    
    
        Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
        FilePicker.AllowMultiSelect = False
        FilePicker.Filters.Add "Excel", "*.xls*", 1
        FilePicker.InitialFileName = "C:\Users\"
        FilePicker.Title = "Please Select the Excel Data..."
        FilePicker.Show
        
        If FilePicker.SelectedItems.Count <> 0 Then
        
            'Delete table records
            CurrentDb.Execute "DELETE * FROM tblImportFM_BRP", dbFailOnError
            CurrentDb.Execute "DELETE * FROM tblAppImportFM_BRP", dbFailOnError
                    
            'Select one file
            SelectedFile = FilePicker.SelectedItems(1)
            
            'Transfer Information
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImportFM_BRP", SelectedFile, True, "CC BRP!"
    
    
            'Open Tables for review
            DoCmd.OpenTable "tblImportFM_BRP", acViewNormal, acEdit '
            
            'Execute Append Query (converting info from import and updating into appended table)
            dbs.Execute "appqryImportFM_BRP", dbFailOnError
           
            'Message when successful
            MsgBox ("The data has been successfully loaded")
            
            'Message if no file was selected
            Else
                Call MsgBox("No file was selected.")
        End If
    End Sub

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Perhaps open the Excel file first and do some checks to see if it is the correct workbook? Check sheet names, cells etc?

    I've added simple error trapping. Please also review the link I posted for more info.
    You would check for the error number and post your own message. Best to stop the error in the first place if possible though?

    Code:
    • Private Sub cmdBRP_Click()Dim dbs As DAO.Database Dim SelectedFile As String Dim FilePicker As FileDialog Dim SQLdelete As String Set dbs = CurrentDb Dim strXls As String On Error GoTo Err_Display Set FilePicker = Application.FileDialog(msoFileDialogFilePicker) FilePicker.AllowMultiSelect = False FilePicker.Filters.Add "Excel", "*.xls*", 1 FilePicker.InitialFileName = "C:\Users\" FilePicker.Title = "Please Select the Excel Data..." FilePicker.Show If FilePicker.SelectedItems.Count <> 0 Then 'Delete table records CurrentDb.Execute "DELETE * FROM tblImportFM_BRP", dbFailOnError CurrentDb.Execute "DELETE * FROM tblAppImportFM_BRP", dbFailOnError 'Select one file SelectedFile = FilePicker.SelectedItems(1) 'Transfer Information DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImportFM_BRP", SelectedFile, True, "CC BRP!" 'Open Tables for review DoCmd.OpenTable "tblImportFM_BRP", acViewNormal, acEdit ' 'Execute Append Query (converting info from import and updating into appended table) dbs.Execute "appqryImportFM_BRP", dbFailOnError 'Message when successful MsgBox ("The data has been successfully loaded") 'Message if no file was selected Else Call MsgBox("No file was selected.") End If Err_Exit: Exit Sub Err_Display: MSGBOX "Error has occurred " & Err.Number & " " & Err.Description Resume Err_Exit End Sub
    https://excelmacromastery.com/vba-error-handling/
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As Welshgasman is alluding, it is always better to prevent errors rather than trap them, which I why I made the comment about your procedure. I could not understand why you would delete records (or continue any process beyond verifying if a user action was correct) only to discover that the wrong file was chosen. If you have a way to test that action I would add it and exit if the action taken was wrong. The only thing I'd add to the last code is that IMO, if you ever create an object variable (SET anything), it's usually considered good practice to destroy it (variable = Nothing). You would do that in the exit block (in this case, Err_Exit).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Thank you for the reference and help! Works great and learned!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import an Excel worksheet into Access?
    By Dave Lambert in forum Import/Export Data
    Replies: 1
    Last Post: 09-18-2018, 01:52 PM
  2. Replies: 7
    Last Post: 09-24-2013, 04:53 AM
  3. Import dynamic Excel worksheet ranges
    By silverspr in forum Programming
    Replies: 1
    Last Post: 03-09-2013, 02:28 PM
  4. Import excel worksheet by range with non identical headers
    By snoopy2003 in forum Import/Export Data
    Replies: 1
    Last Post: 03-14-2011, 01:04 AM
  5. VBA to Import Excel Worksheet
    By bdaniel in forum Programming
    Replies: 2
    Last Post: 11-23-2010, 10:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums