Results 1 to 6 of 6
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Import code works for me, but not other users

    I'm using the below VBA and module to allow users to import excel data into a temp table and then append into the main table after cleaning up the raw data a bit. The import method works flawlessly for me (on different devices too) but does not work for other users. I've asked two different users on two different devices to try it using the exact same excel file as me but they are getting 3349 error codes ("You cannot record your changes because a value you entered violates the settings defined for this table or list (for example, a value is less than the minimum or greater than the maximum). Correct the error and try again.")

    I don't understand how it could be violating the settings defined for the table if the very same data is imported fine for me. Any ideas?

    Browse and import button:
    Code:
    Option Compare Database
    
    Private Sub btnBrowse_Click()
    
        Dim diag As Office.FileDialog
        Dim item As Variant
        
        Set diag = Application.FileDialog(msoFileDialogFilePicker)
        diag.AllowMultiSelect = False
        diag.Title = "Please select an Excel Spreadsheet"
        diag.Filters.Clear
        diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx"
        
        If diag.Show Then
            For Each item In diag.SelectedItems
                Me.txtFileName = item
            Next
        End If
        
    btnBrowse_Click_Exit:
        Exit Sub
    
    
    End Sub
    
    
    Private Sub btnImportSpreadsheet_Click()
                   
        DoCmd.SetWarnings (WarningsOff)
        DoCmd.OpenQuery "qryClearImportList"
        
        Dim FSO As New FileSystemObject
        
        If Nz(Me.txtFileName, "") = "" Then
            MsgBox "Please select a file to import."
            Exit Sub
        End If
        
        If FSO.FileExists(Nz(Me.txtFileName, "")) Then
            moduleImportEventSpreadsheet.ImportExcelSpreadsheet Me.txtFileName, "tblComplaintImportTemp"
        Else
            MsgBox "File not found."
        End If
                
        DoCmd.SetWarnings (WarningsOff)
        DoCmd.OpenQuery "qryFirstNameUpdates"
        DoCmd.OpenQuery "qryLastNameUpdates"
        DoCmd.OpenQuery "qryCleanCustomerID"
        DoCmd.OpenQuery "qryCustomerIDUpdates"
        DoCmd.OpenQuery "qryNewComplaintsAppend"
        DoCmd.OpenQuery "qryClearTempTable"
        DoCmd.Close
    
    
        StrResponse = MsgBox("Process Complete!")
            
    btnImportSpreadsheet_Click_Exit:
        Exit Sub
        
    End Sub




    Import Module:
    Code:
    Option Compare DatabaseOption Explicit
    
    
    Public Sub ImportExcelSpreadsheet(filename As String, tableName As String)
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, filename, True
    
    End Sub


  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    are you using UNC file paths? i.e: \\server\folder\file.xls instead of Drive letters? do not use drive letters on filenames. (unless its the C: personal drive)

    is everyone on the same version of Excel?

  3. #3
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Quote Originally Posted by ranman256 View Post
    are you using UNC file paths? i.e: \\server\folder\file.xls instead of Drive letters? do not use drive letters on filenames. (unless its the C: personal drive)

    is everyone on the same version of Excel?
    Yes, all the same version of excel. For the file paths, I personally have the excel file in my downloads folder and the others have it in their personal downloads folder as well. Is that the issue you think?

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I finally got your code to (mostly) work. I do not have your queries, so I had to skip over them - therefore, they are untested.
    I did make changes to your code....

    There needs to be a reference set to "Microsoft Office XX.0 Object Library" where XX refers to your version number. I have A2010, so my version number is 14. My A365 version is 16.

    Apparently, you have a module named "moduleImportEventSpreadsheet" with a sub named "ImportExcelSpreadsheet".
    You do not need to have the module name when calling the sub.

    Use
    Code:
        If FSO.FileExists(Nz(Me.txtFileName, "")) Then
             ImportExcelSpreadsheet Me.txtFileName, "tblComplaintImportTemp"
        Else
            MsgBox "File not found."
        End If
    OR
    Code:
        If FSO.FileExists(Nz(Me.txtFileName, "")) Then
            Call ImportExcelSpreadsheet (Me.txtFileName, "tblComplaintImportTemp")    '<<-- note the parenthesis
        Else
            MsgBox "File not found."
        End If
    In any case, you do not need to use a standard module.

    Here is the browse code:
    Code:
    Private Sub btnBrowse_Click()
    
        Dim diag As Office.FileDialog
        Dim item As Variant
    
        Me.txtFileName = vbNullString
    
        Set diag = Application.FileDialog(msoFileDialogFilePicker)
        With diag
            .AllowMultiSelect = False
            .Title = "Please select an Excel Spreadsheet"
            .Filters.Clear
            .Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx"
    
            If .Show Then
                Me.txtFileName = .SelectedItems(1)
            End If
        End With
    
        'btnBrowse_Click_Exit:    <<-- Not needed. NO error handler implemented!!
        '    Exit Sub
    
    End Sub
    You have ".AllowMultiSelect = False", but then use a For...Next loop when there can only be one selection. I changed it.



    The ImportSpreadsheet code:
    Code:
    Private Sub btnImportSpreadsheet_Click()
    
    '    Dim FSO As New FileSystemObject
        Set FSO = CreateObject("scripting.FileSystemObject")
        Dim d As DAO.Database
    
        Set d = CurrentDb
    
        '    DoCmd.SetWarnings (WarningsOff)
        
        DoCmd.OpenQuery "qryClearImportList"
    
        If Nz(Me.txtFileName, "") = "" Then
            MsgBox "Please select a file to import."
            Exit Sub
        End If
    
        If FSO.FileExists(Nz(Me.txtFileName, "")) Then
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tblComplaintImportTemp", Me.txtFileName, True
            '  moduleImportEventSpreadsheet.ImportExcelSpreadsheet Me.txtFileName, "tblComplaintImportTemp"
        Else
            MsgBox "File not found."
        End If
    
        '    DoCmd.SetWarnings (WarningsOff)
    
        d.Execute "qryFirstNameUpdates", dbFailOnError
        d.Execute "qryLastNameUpdates", dbFailOnError
        d.Execute "qryCleanCustomerID", dbFailOnError
        d.Execute "qryCustomerIDUpdates", dbFailOnError
        d.Execute "qryNewComplaintsAppend", dbFailOnError
        d.Execute "qryClearTempTable", dbFailOnError
    
        '    DoCmd.Close  '<<-- what are you closing? If the form use
        DoCmd.Close acForm, Me.Name
    
        Set d = Nothing
        Set FSO = Nothing
        
        MsgBox ("Process Complete!")
    '    StrResponse = MsgBox("Process Complete!")
    
        '    btnImportSpreadsheet_Click_Exit:       <<-- Not needed. NO error handler implimented!!
        '    Exit Sub
    
    End Sub
    For the DoCmd.TransferSpreadsheet command, instead of acSpreadsheetTypeExcel12 (A2007), you should use acSpreadsheetTypeExcel12Xml (A2010 and later)
    See AcSpreadSheetType

  5. #5
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    Okay I will try that. Just to be clear, are you saying to just delete the module entirely?

    Thank you!

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just to be clear, are you saying to just delete the module entirely?
    I don't know what other code you might have in that module - so maybe yes - maybe no.

    I'm just saying that I would have the "DoCmd.Transferspreadsheet" command in the "Sub btnImportSpreadsheet_Click()" code in this case.
    It all depends if you call "Sub ImportExcelSpreadsheet()" anywhere else in your code. If you do call it from other forms, then leave the command in the module.

    I just was trying to tighten up the code. To me, it makes sense to have the "DoCmd.Transferspreadsheet" command in the "Sub btnImportSpreadsheet_Click()" code.
    The way you have the code is not wrong (other than how you were calling the sub in the standard module)... But I would write it a little different - different styles. After all, why have 3 subs when you could have 2? (less code to maintain)


    Good luck with your project......

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

Similar Threads

  1. Replies: 1
    Last Post: 08-05-2019, 11:35 AM
  2. Replies: 4
    Last Post: 04-25-2019, 01:29 PM
  3. Replies: 8
    Last Post: 10-22-2017, 12:54 PM
  4. Import from MS Works
    By jerald in forum Import/Export Data
    Replies: 0
    Last Post: 03-17-2009, 08:00 AM
  5. Replies: 1
    Last Post: 03-31-2008, 09: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