Hello !

The Jet engine allows to retrieve the contents of a range in a recordset, with
queries like :

SELECT * FROM [Sheet1$A1:B4]

Problem : suppose that in this sheet, only the cells A4:B4 are populated (all
other cells are empty).

In this case, the recordset built by the above query will have only 1 record,
containing the values of A4:B4, and not what we expect (A1:B4).

Is it possible to obtain the right records, ie :

Record 1 : Null - Null
Record 2 : Null - Null
Record 3 : Null - Null
Record 4 : Value of A4 - Value of B4


Thanks !

Hello Laurent,

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. If I'm off base, please feel free to let me know.

As you see, "SELECT * FROM [Sheet1$A1:B4]" does not return the value in
A1:B3 because Sheet1$ only refers to the used range in Sheet1. The used
range starts at the upper left-most cell in the worksheet that contains
data. As A1:B3 contain no data, they are not included into Sheet1$ and
"SELECT * FROM [Sheet1$A1:B4]" is not able to return them by design.

The possible workarounds a
#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)
a. In the worksheet, select A1:B4.
b. In the upper left corner, there is a "Name Box". Please input a name
into it, for instance, "Table1"
c. Replace the SQL string as SELECT * FROM Table1

#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)
If your environment permits, you could also use Office Excel Automation to
have a full control of the worksheet programmatically. Suppose that you are
using C#, please refer to the KB article:
"Binding for Office automation servers with Visual C# .NET"
http://support.microsoft.com/kb/302902
If you are using Visual Basic, please refer to
http://support.microsoft.com/kb/q219151/
When we get the Worksheet object (e.g oSheet), we could use
oSheet.Range("A1", "B4") to get the values.

#Workaround 3. Insert empty rows into the result data table
(Please note that this workaround only works when you know the border of
used range in the worksheet)
If the border of the used range in the worksheet is known, we could
calculate the empty rows to be inserted. For instance, if A4:B4 is the
border of used range, we could figure out that we need to insert 3 empty
rows because of the gap between A1:B4 and A4:B4.

If all the workarounds do not help to resolve the problem, would you let me
know the information below so that I could try to find a specific
resolution for your situation. I am looking forward to your reply.
a. How do you distribute the application?
b. How do you distribute the Excel workbook? Is it distributed in the
server and all the clients read data from it? Is the workbook updated
frequently? Do you have the control of its update process?

Sincerely,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hello Laurent,

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. If I'm off base, please feel free to let me know.

As you see, "SELECT * FROM [Sheet1$A1:B4]" does not return the value in
A1:B3 because Sheet1$ only refers to the used range in Sheet1. The used
range starts at the upper left-most cell in the worksheet that contains
data. As A1:B3 contain no data, they are not included into Sheet1$ and
"SELECT * FROM [Sheet1$A1:B4]" is not able to return them by design.

The possible workarounds a
#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)
a. In the worksheet, select A1:B4.
b. In the upper left corner, there is a "Name Box". Please input a name
into it, for instance, "Table1"
c. Replace the SQL string as SELECT * FROM Table1

#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)
If your environment permits, you could also use Office Excel Automation to
have a full control of the worksheet programmatically. Suppose that you are
using C#, please refer to the KB article:
"Binding for Office automation servers with Visual C# .NET"
http://support.microsoft.com/kb/302902
If you are using Visual Basic, please refer to
http://support.microsoft.com/kb/q219151/
When we get the Worksheet object (e.g oSheet), we could use
oSheet.Range("A1", "B4") to get the values.

#Workaround 3. Insert empty rows into the result data table
(Please note that this workaround only works when you know the border of
used range in the worksheet)
If the border of the used range in the worksheet is known, we could
calculate the empty rows to be inserted. For instance, if A4:B4 is the
border of used range, we could figure out that we need to insert 3 empty
rows because of the gap between A1:B4 and A4:B4.

I am still doing researches to see if there are other workarounds for you.

If all the workarounds above cannot help to resolve the problem, would you
let me know the information below so that I could try to find a specific
resolution for your situation. I am looking forward to your reply.
a. How do you distribute the application?
b. How do you distribute the Excel workbook? Is it distributed in the
server and all the clients read data from it? Is the workbook updated
frequently? Do you have the control of its update process?

Sincerely,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

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 !

Hello Laurent,

According to the caution mentioned in KB
http://support.microsoft.com/kb/257819

"A caution about specifying worksheets: The provider assumes that your
table of data begins with the upper-most, left-most, non-blank cell on the
specified worksheet. In other words, your table of data can begin in Row 3,
Column C without a problem. However, you cannot, for example, type a
worksheeet title above and to the left of the data in cell A1."

That is to say, Excel ADO supposes that the range in Sheet1$A1:B4 is within
the used range. If it is outside the used range, some unexpected behavior
might happen. We feel sorry if this behavior of Excel ADO does not fit your
situation. And please feel free to give your feed back at
http://connect.microsoft.com. (Visual Studio and .NET Framework).
Developers from Microsoft product team will regularly check customers'
feedbacks and try to enhance the products. Thanks a lot for your
understanding.

If you encounter any problem when using automation, please feel free to let
me know. We will do our best to help you.

Sincerely,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hello,

I am writing to check if you encounter any problem when using Office
automation to retrieve data from the worksheet. Please feel free to let me
know if you have any question about it. Besides, the developers from
Microsoft Jet team helped to confirm that it is an issue of our product
that has an endless loop when the query range is not in used range of a
worksheet. Looking at the nature of this issue, it would require intensive
troubleshooting which would be done quickly and effectively with direct
assistance from a Microsoft Support Professional through Microsoft Product
Support Services. If you insist on using Jet approach, you can contact
Microsoft Product Support directly to discuss additional support options
you may have available, by contacting us at 1-(800)936-5800 or by choosing
one of the options listed at
http://support.microsoft.com/common/...fh;en-us;cntac
tms. Please note that if it is confirmed to be an issue of our product, all
the costs of the support incident will be refunded.

Have a nice day!

Sincerely,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.