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.