VBA to determine version of excel file code transfer spreadsheet import help

[sxschech]

After some searching, I found a line of code on msdn for how to determine what version of excel a file was saved as. I would like to use that or something like it to find this out so that I know what acSpreadsheetTypeExcel to use when importing with transferspreadsheet. I had been using this, but doesn't seem to work if the file being imported is an earlier version than the choices I provide in the code:

If Right(stimport, 1) = "X" Then
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, stTableName, stimport, True
    Else
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, stTableName, stimport, True
End If

This is what I found
[tt]ActiveWorkbook.FileFormat[/tt]

I looked around for code to read the excel file and tried putting it in an access vba module like this:

Public Function ExcelVersion(ByVal stfilepath As String)
'Not working
'FOUND FILE FORMAT AT [URL unfurl="true"]https://msdn.microsoft.com/en-us/library/office/ff840717.aspx[/URL]
    Set objApp = CreateObject("Excel.Application")
    objApp.Visible = True
    Set wb = objApp.Workbooks.Open(stfilepath, True, False)
    ExcelVersion = ActiveWorkbook.FileFormat
    wb.Close
  
    objApp.Quit

    Set objApp = Nothing
End Function

It sort of works. The first time I run, it gives the right result, then if I run it again, it either gives a run-time error 91 Object variable or With block variable not set. Or it returns the wrong version number; that is to say a subsequent call to the function returns whatever the last version it found. Based on the msdn article: closed the web page so can't reference it just now... 56 is the version for xls 97-2003 and 51 is for xlsx. Hopefully it is possible to get this functioning properly by someone who knows more about referring to excel in vba from access.

In case is needed, running on 8.1; Office 2013; my collegue is on Win 7 and Office 2007, but we both get excel files in earlier versions from outside sources, so can't tell them to use a newer version. Also would prefer not to have to open and save as in excel before importing.

 

[PHV]

I'd use this:
ExcelVersion = [!]wb[/!].FileFormat

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sxschech]

Thanks PHV that simple change seemed to do the trick. I found the other web site link that I prematurely closed on the enumeration. It is [tt]

https://msdn.microsoft.com/en-us/library/office/ff198017.aspx

[/tt]. Below is the revised function for reference.

Public Function ExcelVersion(ByVal stfilepath As String)
'[URL unfurl="true"]https://msdn.microsoft.com/en-us/library/office/ff840717.aspx[/URL]
'[URL unfurl="true"]https://msdn.microsoft.com/en-us/library/office/ff198017.aspx[/URL]
    Set objApp = CreateObject("Excel.Application")
    objApp.Visible = True
    Set wb = objApp.Workbooks.Open(stfilepath, True, False)
    ExcelVersion = wb.FileFormat
    wb.Close
  
    objApp.Quit

    Set objApp = Nothing
    
    Select Case ExcelVersion
        Case 39
            ExcelVersion = 5 'excel7
        Case 50, 51
            ExcelVersion = 9 'excel12 (2007-2013, xlsx)
        Case 56
            ExcelVersion = 8 'excel8 (97-2003 format in Excel 2007-2013, xls)
    End Select
End Function