First, I would suggest that you support workbooks created with Excel 95 or
older, only if absolutely necessary. In other words, if the chance of
someone using your application with an older file version is slight, then
maybe you should just accept that risk. Trap the error, if you can, and show
a message saying "Please upgrade the file format to Excel 97 or newer.
Here's how..."
If you must support older file formats, then you might test the Excel 8.0
source database type with the older files. You might find that the OLE DB
provider, like Excel itself, is backward compatible.
If that doesn't work, then you have three possible options: (1) If your
application has access to Excel, then you could open the workbook
programatically and check its FileFormat property as shown in the two
functions below. As you can see, it's not quite as simple as you might
think, especially if you cannot rely on a specific version of Excel being
available to open the file. (2) Check the Jet OLEDB:Engine Type property
(see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnac...
l/adoproperties.asp). This might be a Catch 22, since you have to open the
connection to check this property. (3) Dig into the guts of the Excel file
to find its version (see the Excel Developers Kit available from Microsoft
Press).
'''
''' Function: GetWorkbookExcelVersion
'''
''' Comments: This function returns the Excel version that
''' created a workbook file.
'''
''' Arguments: WorkbookPath is the path of the workbook to check.
'''
''' Returns: The workbook file format as a 32-bit integer.
'''
''' Keywords: WORKBOOK FILE FORMAT EXCEL VERSION
'''
''' Date Developer Action
''' ------------------------------------------------------------------
''' 03/01/99 Scott Hutchinson Created
''' 05/01/02 Scott Hutchinson Adapted for Excel 10 and VB .NET.
'''
Public Shared Function GetWorkbookExcelVersion(ByVal WorkbookPath As String)
As Long
Dim appXL As New Excel.Application()
Dim wbk As Excel.Workbook
wbk = appXL.Workbooks.Open(Filename:=WorkbookPath, UpdateLinks:=False,
ReadOnly:=True)
With wbk
Dim AppExcelVersion As Long
Const XL_EXCEL5 As Long = 39&
AppExcelVersion = ExcelAppVersion(appXL)
If AppExcelVersion > 7 Then
Select Case .FileFormat
Case Excel.XlFileFormat.xlWorkbookNormal '-4143
GetWorkbookExcelVersion = 8 '"Excel 97 or newer"
Case CType(XL_EXCEL5, Excel.XlFileFormat)
GetWorkbookExcelVersion = 5 '"Excel 5/95"
Case Else
' If version unknown, then return 5 to use the Excel 5.0 source database
type.
GetWorkbookExcelVersion = 5 '"Unknown version (older than Excel 5 or
non-Excel format)"
End Select
Else
Select Case .FileFormat
Case Excel.XlFileFormat.xlWorkbookNormal
GetWorkbookExcelVersion = 5 '"Excel 5/95"
Case Else
' If version unknown, then return 5 to use the Excel 5.0 source database
type.
GetWorkbookExcelVersion = 5 '"Unknown version (older than Excel 5 or
non-Excel format)"
End Select
End If
.Close(savechanges:=False)
End With ''' wbk
appXL.Quit()
appXL = Nothing
End Function ''' GetWorkbookExcelVersion
'''
''' Function: ExcelAppVersion
'''
''' Comments: This function gets the integer portion of the
''' version of Excel that is running. This function assumes that
''' the Application.Version property will always return a version
''' string that includes a period, regardless of whether the local
''' decimal symbol is a period or a comma. This is the behavior of
''' Excel that I have observed through version 8.
'''
''' Arguments: None.
'''
''' Returns: The integer portion of the Excel version number.
'''
''' Keywords: EXCEL VERSION NUMBER
'''
''' Date Developer Action
''' ------------------------------------------------------------------
''' 06/29/99 Scott Hutchinson Assist 1.06a: Created.
'''
Public Shared Function ExcelAppVersion(ByRef appXL As Excel.Application) As
Long
With appXL
ExcelAppVersion = CLng(Left$(.Version, InStr(1, .Version, ".",
vbTextCompare) - 1))
End With '''Application
End Function ''' ExcelAppVersion
Scott Hutchinson
Quote:
> In KB Article Q316934
> http://support.microsoft.com/search/preview.aspx?scid=kb;en-us;Q316934 it
> reads:
> NOTE : Use the Excel 5.0 source database type for Microsoft Excel 5.0 and
> 7.0 (95) workbooks and use the Excel 8.0 source database type for
Microsoft
> Excel 8.0 (97), 9.0 (2000) and 10.0 (2002) workbooks. The examples in this
> article use Excel workbooks in the Excel 2000 and Excel 2002 format.
> How does one programatically check the XL version?
> If I provide option for end user to open XL file, they may not know the
> version and choose the incorrect version.
> Thanks
> Harry
how do I check XL file version programatically?