TransferSpreadsheet Excel xls error, but xlsx works fine

[neelsfer]

I want to be able to import either xls or xlsx files into the same table.
I use the following code and it works 100% for xlsx but gives an error with xls files when importing

Dim strPath As String
 
With Me
    strPath = Left(.lblFile.Caption, InStrRev(.lblFile.Caption, "\"))
    strPath = fsbrowse(strStart:=strPath, _
                       lngType:=msoFileDialogFilePicker, _
                       strPattern:="MS Excel,*.XLS; *.XLSX")
 
    If strPath > "" Then
        .lblFile.Caption = strPath
        Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, "tblDepotInvImport1", strPath, True, "")
    End If
End With

the module i use

Public Function fsbrowse(Optional strStart As String = "", _
                         Optional lngType As MsoFileDialogType = _
                             msoFileDialogFolderPicker, _
                         Optional strPattern As String = "MS Excel,*.XLS; *.XLSX")
 
    Dim varEntry As Variant
    Dim fdf As FileDialogFilter
'acSpreadsheetTypeExcel12xml
    fsbrowse = ""
    With Application.FileDialog(dialogType:=lngType)
        'Set the title to match the type used from the list
        .Title = "Browse for "
        Select Case lngType
        Case msoFileDialogOpen
            .Title = .Title & "File to open"
        Case msoFileDialogSaveAs
            .Title = .Title & "File to SaveAs"
        Case msoFileDialogFilePicker
            .Title = .Title & "File"
        Case msoFileDialogFolderPicker
            .Title = .Title & "Folder"
        End Select
 
        Call .Filters.clear
        For Each varEntry In Split(strPattern, "~")
            Call .Filters.add(Description:=Split(varEntry, ",")(0), _
                              Extensions:=Split(varEntry, ",")(1))
        Next varEntry
        'Set some default settings
        .InitialFileName = strStart
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewDetails
        'Only return a value from the FileDialog if not cancelled.
        If .Show Then fsbrowse = .SelectedItems(1)
    End With
End Function

Error message " external table is not in the expected format" The xls file also do not preview, as xlsx does when clicking on it to import.
Please advise its is driving me to ......

[twinnyfo]

.xls and .xlsx are different types of files, so the import engine is expecting certain things based on the type of file you designate.

If you know you are going to be importing a .xls file, try using the parameter of acSpreadsheetTypeExcel8. There are various ways you can have the VBA determine the file type.

[NeoPa]

Neels,

At this stage I'm surprised you're posting a question with so much unnecessary information and distraction in it. You can easily determine if the value in strPath matches what you expect it to, and thus realise posting all the code related to FSBrowse() is irrelevant to your problem. If that string had been wrong after being returned from FSBrowse() that would be another matter. That is debugging first base. You should have done at least that much before posting the question.

As for the problem, look at line #11 of the (tidied version of the) first code section of your post.
acSpreadsheetTypeExcel9 is the second parameter. If you read the documentation you'll see that this parameter (named SpreadsheetType) specifies the file format of the file that's being dealt with (in this case read from). For files of type .XLS you will need to select a value that matches that type of file. I expect a good place to start would be acSpreadsheetTypeExcel8, but suck it and see.

While we're happy to help where we can, it's generally a good idea to do a bit of work first trying to resolve your problem. When, as in this case, it appears that you haven't done even the very basics for yourself before posting the question, you might find people less willing in future to spend their time trying to help you.

[neelsfer]

Hi Neopa. I have tried for 2 solid evenings using all sorts of different options and acspreadsheettypes, and researched the net. I use Excel 2007 and it only imports xlsx files with this code, although i can view xls or xlsx file names, when opening it with the "msoFileDialogFilePicker". The error message is then :"external table is not in the expected format".

I would prefer to only import xls as the default format, because i receive the Excel files like that. I now have to open and re-save in xlsx within Excel, in order to import. Its a pain. I have also set the reference to the Microsoft Excel 12.0 Object library with no success.

I will include 2 screen shots. In the problem xls one, the file will not preview while the other xlsx it does. This file in question does however open in Excel 2007 in compatibility mode.
NeoPa its not lack of effort, i am totally stuck! Please advise.

[NeoPa]

I hear you Neels. If you say you did try then I'm happy to believe you, but I'm a little surprised that after a long time working in Access you don't seem to be using some of the basic approaches we've been telling you about for a long time.

Debugging.
With a little basic debugging (See Debugging in VBA) you should be able to see very easily that the problem is purely and simply with the TransferSpreadsheet() line of code. I cannot emphasise too much how important debugging is in the life-cycle of a code project. Without it you can only ever be a small fraction as effective as a developer. The tools are there so use them. I'm sure you'll love it when you do.

For all the trying and trouble you're having - you haven't replied to the suggestion made, both by Twinnyfo & myself, that you try using acSpreadsheetTypeExcel8 as the second (SpreadsheetType) parameter. Have you tried that? If so, what did you see? Did anything change? If so, what?

You will find - I suspect you've already found - that however much time and effort you put into solving your problem, will be so much wasted time and effort until you stop running round in circles and start applying intelligent reasoning to the problem. I suspect the answer is there on the screen in front of you, but you've been so heavily into the problem as you understand it that you haven't even seen it.

I suspect we've all been in that same place. Worried so much about something that we haven't let our intelligence lead us through the easiest path. That is to say we expend so much effort everywhere in our confusion that we don't have any left for seeing the next logical step - even though that next step would lead us straight to the way out of the maze.

NB.
Remember, when dealing with people on a forum it always makes sense to respond to every suggestion. Other than common politeness, it also allows them to understand they can move forward or look elsewhere. Without any response it will generally be assumed that we are still waiting on you to test it.