FIX: Excel ODBC Driver Cannot Open Excel 4.0 XLS Files

If you try to open a Microsoft Excel 4.0 file, the ODBC Microsoft Jet driver may return the following error message:

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This bug was corrected in Microsoft Jet 4.0 Service Pack 5. For additional information about the latest Jet 4.0 service pack, click the following article number to view the article in the Microsoft Knowledge Base:

Steps to Reproduce Behavior

The following steps only reproduce the error on a computer with Microsoft Jet 4.0 Service Pack 4 or earlier.

1. Create a data source name (DSN) called ExcelTest for an Excel 4.0 file.
2. Create a new Standard EXE project in Microsoft Visual Basic.
3. Set a project reference to Microsoft ActiveX Data Objects 2.6..
4. Paste the following code into the code section of Form1:

   Option Explicit
   
   Const strConn = "DSN=ExcelTest"
   Const strSQL = "SELECT * FROM [Sheet1$]"
   
   Private Sub Form_Load()
       Dim cn As ADODB.Connection
       Set cn = New ADODB.Connection
       cn.Open strConn
       
       Dim rs As ADODB.Recordset
       Set rs = New ADODB.Recordset
       With rs
           .CursorLocation = adUseClient
           .Open strSQL, cn
           
           Debug.Print .Fields(0)
           
           .Close
       End With
       
       Set rs = Nothing
       cn.Close
       Set cn = Nothing
   End Sub
   					

5. Run the project. Notice that you receive the above-mentioned error message.