Results 1 to 11 of 11
  1. #1
    mlrucci is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2018
    Posts
    202

    Error3251 Operation not supported for this object type


    Hello, I am stumped. I have a code that has been working without issues. Doing QC, I am running into error 3251. I have reviewed the spreadsheets from excel and do not see anything different. What is bizzar, this code works fine for the other imports/buttons. I have done compact and repair, debug, created new form new button and same code, and moved all into another DB for possible corruption. Weird. Any suggestions?

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub Command0_Click()
    Dim FilePicker      As FileDialog
    Dim intLine         As Long
    Dim SelectedFile    As String
    Dim dbs             As DAO.Database
    Dim vrtSelectedItem As Variant
    Set dbs = CurrentDb
    Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
            
    On Error GoTo Err_Display
        CurrentDb.Execute "DELETE * FROM tblImportHR_CC", dbFailOnError '
        CurrentDb.Execute "DELETE * FROM tblAppImportHR_CC", dbFailOnError '
        FilePicker.AllowMultiSelect = True
        FilePicker.Filters.Add "Excel", "*.xls*", 1
        FilePicker.InitialFileName = "C:\Users\"
        FilePicker.Title = "Please Select the Excel Data..."
            
            If FilePicker.Show = -1 Then
                For Each vrtSelectedItem In FilePicker.SelectedItems
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImportHR_CC", vrtSelectedItem, True, "L:P"
                Application.FollowHyperlink vrtSelectedItem
            Next
                DoCmd.OpenForm "frmImportHR_CC", acViewNormal, acEdit
                MsgBox ("The data has been successfully loaded")
                Else
                    Call MsgBox("No file was selected.")
            End If
    Err_Exit:
        Exit Sub
    Err_Display:
        MsgBox "An error has occurred.  Please review the workbook you have selected.  The most common error(s) is/are you choose the wrong workbook or there is/are extra data cells not recognized. Otherwise, please check error " & Err.Number & " " & Err.Description
        Resume Err_Exit
    End Sub

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Very nice post: uses code tags, provides error number, provides error message text. The only thing missing is which line causes the error.
    I don't have the form or table or a spreadsheet that will match but can rem out the action queries and run that code up to the transfer and open a spreadsheet if I bypass that line. I imagine the issue is the transfer line. Perhaps it is because the highest version I found for the acSpreadSheetType is 10?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Try a decompile?
    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

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I bet the error is caused by this line:
    Code:
    FilePicker.Filters.Add "Excel", "*.xls*", 1
    Unfortunately the Access file dialog does not allow filters:
    https://www.access-programmers.co.uk...ave-as.309593/
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Gicu View Post
    I bet the error is caused by this line:
    Code:
    FilePicker.Filters.Add "Excel", "*.xls*", 1
    Unfortunately the Access file dialog does not allow filters:
    https://www.access-programmers.co.uk...ave-as.309593/
    Cheers,
    The posted code is not opening as Save As. Works for me.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Gicu View Post
    I bet the error is caused by this line:
    Code:
    FilePicker.Filters.Add "Excel", "*.xls*", 1
    Unfortunately the Access file dialog does not allow filters:
    https://www.access-programmers.co.uk...ave-as.309593/
    Cheers,
    Sorry but that isn't true. Nor are many of the comments by the OP in the link
    You can filter for one or more types of file using FileDialog.FilePicker
    The position number argument is only needed of you want to specify the order

    For example I have this code:
    Code:
    Public Function FilePicker(Optional strFileName As String, _    Optional ByVal PreviousPath As Variant, _
        Optional ByVal strWindowTitle As String = "Select a file") As String
    
        Dim fd As Office.FileDialog
        
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        
        With fd
            .Title = strWindowTitle
            .Filters.Add "All files", "*.*", 1
            .Filters.Add "Excel Workbooks", "*.xls;*.xlsx;*.xlsm", 2
            .Filters.Add "Word", "*.docx;*.doc", 3
            .Filters.Add "Powerpoint", "*.ppt;*.pptx", 4
            .Filters.Add "PDF", "*.pdf", 5
            .Filters.Add "Outlook Msg", "*.msg", 6
            .AllowMultiSelect = False
            If .Show = -1 Then
                FilePicker = .SelectedItems(1)
            Else
                FilePicker = vbNullString
            End If
        End With
        Set fd = Nothing
    End Function
    Using that code gives this result:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	31 
Size:	53.6 KB 
ID:	46296
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Thanks Micron and Colin, I wonder why the filter works for selecting files and not for saving them. I was trying to help someone in a recent post and I did have problems with that on the "save as" method and that is how I got to the link I posted. For many years I was using Ken Getz' GetSaveFile and GetOpenFile code without any problems... Not so easy anymore...

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm trying to think of a reason why I'd want the Save As option when working in Access? It's not like I'm going to open a file using Access code and do a save as. I'd just use the native app. The file wouldn't be an attachment in my db anyway so I couldn't say if opening the attachment in Access to do a save as would make any sense even if it would work.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I was talking about using the file dialog to allow the user to browse to a folder and enter a file name, like when exporting a query to Excel using Docmd.TransferSpreadsheet. You cannot specify the extension you want to filter on.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    mlrucci is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2018
    Posts
    202
    Thank you for all your responses. Sorry for the delay, I have been on vacation. It is weird that some of the buttons with the exact same code (but for different file imports) worked in this db, and others didn't. Then after testing further, then they all stopped working. In addition, if I import the same form from a back up, it will not work in the active db, but works in the back up. In my opinion, the only conclusion, the db is corrupt, which really sucks! Not sure what or why, but I am going to have to rebuild from the last back up forward. It's like there is a bug eating at my program. I even ran a virus check. hummmmm

  11. #11
    mlrucci is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2018
    Posts
    202
    Update, it appears that the tables I created for the import were became corrupt. I recreated the tables and all is working. Frustrating but relieved found the problem! Thanks for the help everyone!

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

Similar Threads

  1. Replies: 9
    Last Post: 06-30-2021, 02:10 AM
  2. Replies: 7
    Last Post: 04-20-2018, 08:48 AM
  3. Runtime Error 3251 object not supported
    By avanduyvenbode in forum Programming
    Replies: 2
    Last Post: 10-18-2016, 09:54 AM
  4. Object Not Supported
    By CementCarver in forum Programming
    Replies: 14
    Last Post: 06-19-2013, 04:33 PM
  5. Operation is not supported for this type of object error
    By CementCarver in forum Programming
    Replies: 4
    Last Post: 06-17-2013, 02:04 PM

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