Hello Jialang and thanks for your reply,

From your post, my understanding on this issue is: you wonder how to
retrieve the value in Excel Sheet1$A1:B4 even if cells of A1:B3 contain no
value.

Exactly.

#Workaround 1. Define a named range (e.g. "Table1") for A1:B4, then use the
named range in the query SQL: SELECT * FROM Table1"
(Please note that this workaround only works when we have write access to
the source workbook so that we could add the named range to the worksheet,
please check if the workaround fits your situation)

No, I don't have write access to the source workbook in my application.

#Workaround 2. Use Office Automation rather than Excel ADO
(Please note that this workaround may require a big change of your existing
codes, please check if it fits your current situation)

Yes and no, the advantage of ADO is that it runs much faster than automation. It
would be perfect if it could put somewhere in the database schema the 1st row
and 1st column indices of the sheet's used ranges.

a. How do you distribute the application?

It is just a DLL.

b. How do you distribute the Excel workbook?

The workbook can be anywhere. The DLL contains an exported "ReadExcelRange"
function, with 2 arguments : path of the Excel workbook + range address. It
returns the contents of the range (in a SAFERRAY variant).

The problem is that the function doesn't know how the sheet is structured. It
could begin with empty rows/columns, it could be also empty. The used range is
unknown.

I thought that obtaining this results would be relatively easy with ADO.

BTW, when I try to read the contents of a range which is above the used range,
most of the ADO methods applied on this recordset seem to enter in an endless loop:

#import "Msado15.dll" rename_namespace("AdoNS") rename("EOF", "adoEOF")
using namespace AdoNS;

// ........................
CString Query="SELECT * FROM " + RangeAddress;
bstr_t bstrTemp=Query.AllocSysString();
VARIANT vcmd;
vcmd.vt=VT_BSTR;
vcmd.bstrVal=bstrTemp;
rs-Open(vcmd, dbConnection.GetInterfacePtr(),
AdoNS::adOpenStatic, AdoNS::adLockReadOnly,0);

int rsCount = rs-RecordCount;

If RangeAddress contains the address of the range which is above the used range,
rs-RecordCount seems to enter into an endless loop, as well as many other
properties of the RecordSet object.

Anyway, I think that I will work with automation.

Thanks !