FileDialogFilePicker Filter type returning 'Argument not optional' error

[Jaron]

Here is some code I have for importing an excel binary into my DB as a table. It is supposed to allow the user to choose the file location to import from. I need it to replace, not append to the existing table of the same name. After the user makes a selection, it temporarily renames the existing table for safety, then imports the file under the correct name. Lastly it deletes the original table that was renamed. The run-around is so that if there is a hang-up I don't end up losing the original.



I want to filter the File Dialog box so that the user can only choose files of the correct type. The line that is supposed to do that hangs up though. If I comment it out, everything else runs smoothly. Here's what I have:

Option Explicit
Public Sub Import()
    
     Dim dlgSlct As FileDialog
     Dim strInputFileName As String
     Set dlgSlct = Application.FileDialog(msoFileDialogFilePicker)
     With dlgSlct
        .InitialFileName = CurrentProject.Path
        .InitialView = msoFileDialogViewDetails
        .Filters = "Excel Files (*.xlsb)"
        .AllowMultiSelect = False
        .Title = "Choose a file to import"
     End With
     
     dlgSlct.Show
     
     If dlgSlct.SelectedItems.Count > 0 Then
     
        strInputFileName = dlgSlct.SelectedItems(1)
        DoCmd.Rename "Original Table1", acTable, "Table1"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Table1", _
        strInputFileName, True, "Table1"
        DoCmd.DeleteObject acTable, "Original Table1"
        
        MsgBox "Import complete"
        
    Else
    
        MsgBox "Data was not imported"
        
    End If
    
End Sub

The module is called through a command button on a form which is coded with the following:

Private Sub Command33_Click()
On Error GoTo Command33_Click_Err
Run "Import"
Command33_Click_Exit:
    Exit Sub
Command33_Click_Err:
    MsgBox Error$
    Resume Command33_Click_Exit
End Sub

Any thoughts on this?

[June7]

Try:
.Filters.Clear
.Filters = "Excel Files", "*.xlsb"

[Jaron]

Nope. It doesn't like that. It flags a compile error on the comma: 'expected end of statement'. The only way I can make it stick is with: "Excel Files *.xlsb"
Then when I run it I get the same error as before: 'Argument not optional.' hung on the '.Filters' argument.
Is there any chance I set references to the wrong library? I added in the MSO 14.0 Object Library to get the FilePicker in the first place, but it almost seems as though it doesn't understand the 'Filters' part of it. But then, the 'Filters.Clear' seems to work ok.

[June7]

Sorry, was not paying attention. Here is the correct syntax:

.Filters.Add "Excel Files", "*.xlsb"

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[Jaron]

I thought I'd tried that one already, but I guess not because it worked. Thanks!