File Selector And Import Selected File

[jo15765]

I am wanting to create a File Selector and import the selected file into access table. I have this syntax, but it does not like my TransferSpreadsheet syntax. What do I need to alter in order for the selected spreadsheet to be imported into a table named "Test"? I also have the filter set to *.xls* as it could be a .xls or a .xlsx

Set f = Application.FileDialog(3)


With f
    .AllowMultiSelect = False
    .Title = "Please Select the Excel File To Import"
    .Filters.Add "Excel Files", "*.xls*"


    If .Show = True Then
        For i = 1 To f.SElectedItems.Count
            selectedfile = f.SElectedItems(i)
            DoCmd.TransferSpreadsheet(acImport, , "Test", selectedfile, True)
        Next
    Else
        MsgBox "You clicked Cancel in the file dialog box."
    End If
    
End With

[pbaldy]

What does the variable contain at that point? What does it "not like", and how? I don't use the parentheses, but they may be okay.

[jo15765]

The variable contains the full file path
C:\TestData\TestWorkbook.xlsx

I get a compile error of 'Syntax Error' on the DoCmd line

[pbaldy]

Have you tried taking the parentheses out?

[CJ_London]

looks like you may need to specify the TransferType (the 2nd parameter) - the default may not be the right format for your file - see this link

https://msdn.microsoft.com/en-us/lib.../ff196017.aspx

Also, try removing the brackets - they imply a value will be returned so you need to make it equal to something. Nevere tried it but perhaps it returns a Boolean e.g.

dim OK as variant
OK=DoCmd.TransferSpreadsheet(acImport, , "Test", selectedfile, True)

[jo15765]

Since I can import either a .xls or a .xlsx is there a way for me to check the file extension so I know to use acSpreadsheetTypeExcel9 or use acSpreadsheetTypeExcel12?

Was following this logic -- http://access-excel.tips/access-vba-cocmd-transferspreadsheet/

I altered my syntax to this, but still get the same error

 selectedfile = f.SElectedItems(i)
            ExtFind = Split(selectedfile, ".")
            If ExtFind = ".xls" Then
                param = acSpreadsheetTypeExcel9
            ElseIf ExtFind = ".xlsx" Then
                param = acSpreadsheetTypeExcel12
            End If
            DoCmd.TransferSpreadsheet(acImport, param, "Test", selectedfile, True)

[jo15765]

I got it - thanks for the assistance, removing parens was very ehlpful

Dim ExtFind As String
Dim param As String
selectedfile = f.SElectedItems(i)
ExtFind = Split(selectedfile, ".")
If ExtFind = ".xls" Then
	param = acSpreadsheetTypeExcel9
ElseIf ExtFind = ".xlsx" Then
	param = acSpreadsheetTypeExcel12
End If
DoCmd.TransferSpreadsheet(acImport, param, "Test", selectedfile, True)

[pbaldy]

Happy to help.