Example using ado

[jj2007]

I've been playing a bit, and finally managed to get OpenSchema running in order to get the sheet names:

Windows 7 VM (no Excel, no Access, no Office):

Code Select Expand

opening testjj3.xls;Extended Properties="Excel 8.0;HDR=NO"
The table name is Database
The table name is testjj3$
opening testjj4.xls;Extended Properties="Excel 8.0;HDR=NO"
---------------- open failed ----------- $AdoErr$() Unspecified Error
opening E:\AdoTest\testjj5.xls;Extended Properties="Excel 8.0"
---------------- open failed ----------- $AdoErr$() Unspecified Error
opening .;Extended Properties=dBASE IV;
The table name is OtherDB2
The table name is OtherDB3
The table name is OtherDB4
The table name is World_Co
The table name is World_Countries
opening test2000.mdb
The table name is JJWasHere
opening E:\AdoTest\main2002.mdb
The table name is main

Win10 (Office 2003 installed):

Code Select Expand

opening testjj3.xls;Extended Properties="Excel 8.0;HDR=NO"
The table name is Database
The table name is testjj3$
opening testjj4.xls;Extended Properties="Excel 8.0;HDR=NO"
The table name is Database
The table name is testjj4$
opening E:\AdoTest\testjj5.xls;Extended Properties="Excel 8.0"
The table name is JJ5Test$
The table name is TestB5$
The table name is TestC5$
opening .;Extended Properties=dBASE IV;
The table name is OtherDB2
The table name is OtherDB3
The table name is OtherDB4
The table name is World_Co
The table name is World_Countries
opening test2000.mdb
The table name is JJWasHere
opening E:\AdoTest\main2002.mdb
The table name is main

Full string is e.g.
Provider="Microsoft.JET.OLEDB.4.0";Data Source=testjj3.xls;Extended Properties="Excel 8.0;HDR=NO"

As mentioned earlier, DBase IV works on a per folder basis, but you can read specific files by restricting the data to the sheet name, which happens to be the file name.

[TimoVJL]

Windows 7 SP1 Breaks ADO Compatibility
An ADO-based application that is compiled in Windows 7 SP1 or in Windows Server 2008 R2 SP1 does not run in earlier versions of Windows
"Type Mismatch" error message when you run a VBA macro in a 64-bit version of an Office 2010 application

[jj2007]

Yes, it's messy. My "solution" will be that it works only partially on Win7.
For Win10, I am making progress: the query works for Excel and Access files (but not for Dbase files).

Windows 10:

Code Select Expand

opening testjj3.xls;Extended Properties="Excel 8.0;HDR=NO"
The table name is Database
The table name is TestJJ3$
Query Ole$      $AdoErr$()      All is fine

opening testjj4.xls;Extended Properties="Excel 8.0;HDR=NO"
The table name is Database
The table name is testjj4$
Query Ole$      $AdoErr$()      All is fine

opening E:\AdoTest\testjj5.xls;Extended Properties="Excel 8.0"
The table name is JJ5Test$
The table name is TestB5$
The table name is TestC5$
Query Ole$      $AdoErr$()      All is fine

opening .;Extended Properties=dBASE IV;
The table name is OtherDB2
The table name is OtherDB3
The table name is OtherDB4
The table name is World_Co
The table name is World_Countries
Query Ole$      $AdoErr$()      Unspecified Error

opening test2000.mdb
The table name is JJWasHere
Query Ole$      $AdoErr$()      All is fine

opening E:\AdoTest\main2002.mdb
The table name is main
Query Ole$      $AdoErr$()      All is fine

Windows 7:

Code Select Expand

opening testjj3.xls;Extended Properties="Excel 8.0;HDR=NO"
The table name is Database
The table name is testjj3$
Query: [SELECT * from [Database]]
Query Ole$ $AdoErr$() All is fine

opening testjj4.xls;Extended Properties="Excel 8.0;HDR=NO"
---------------- open failed ----------- $AdoErr$() Unspecified Error

opening E:\AdoTest\testjj5.xls;Extended Properties="Excel 8.0"
---------------- open failed ----------- $AdoErr$() Unspecified Error

opening .;Extended Properties=dBASE IV;
The table name is OtherDB2
The table name is OtherDB3
The table name is OtherDB4
The table name is World_Co
The table name is World_Countries
Query: [SELECT * from World_Countries]
Query Ole$ $AdoErr$() All is fine

opening test2000.mdb
The table name is JJWasHere
Query: [SELECT * from [JJWasHere]]
Query Ole$ $AdoErr$() All is fine

opening E:\AdoTest\main2002.mdb
The table name is main
Query: [SELECT * from [main]]
Query Ole$ $AdoErr$() All is fine

opening E:\AdoTest\test2015.mdb
The table name is main
Query: [SELECT * from [main]]
Query Ole$ $AdoErr$() All is fine

P.S.: All is fine for DBase III+IV but not for DBase II