VB- If first cell with formula is blank, all cells in column returns blank.

Hi all,
I am using ADO to connect to an excel sheet and display the data in
vb form. In excel there is a column named "TAT" which has a formula t
add two othe cell values to it. If any of the two cells is blank the
the TAT col remains blank.
If the very first cell in the TAT column has some value then VB showa
all cell values in that column. But if the first cell is empty then al
cells in tat column are shown blank, even if there are values in othe
cells.

Advertisements

Of course you can try with something like
SELECT ..., Iif(TAT="",Null,TAT) As TAT, ...
but I have a bad feeling about it. It looks like when querying, the field
type is determined by value in first row, and so when for first row TAT was
"", it'll be "" for all non-string values. And for same reason you can't
include original columns into your query too! Maybe you have to add a dummy
row as first into your table - with all numeric values 0 and all string
values "". And in your query filter it out using WHERE clause.

"mnhesh >" <<> wrote in message
news:...
> Hi all,
> I am using ADO to connect to an excel sheet and display the data in a
> vb form. In excel there is a column named "TAT" which has a formula to
> add two othe cell values to it. If any of the two cells is blank then
> the TAT col remains blank.
> If the very first cell in the TAT column has some value then VB showas
> all cell values in that column. But if the first cell is empty then all
> cells in tat column are shown blank, even if there are values in other
> cells.
>
> What's the solution for this?
>
> Thanks
>
>
> ---
> Message posted
>

Advertisements

"Arvi Laanemets" wrote ...
> It looks like when querying, the field
> type is determined by value in first row, and so when for first row TAT was
> "", it'll be "" for all non-string values. And for same reason you can't
> include original columns into your query too! Maybe you have to add a dummy
> row as first into your table - with all numeric values 0 and all string
> values "".

It's not that simple! Standard answer number 4:

The relevant registry keys (for Jet 4.0) are in:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/

The ImportMixedTypes registry key is always read (whether it is
honored is discussed later). You can test this by changing the key to
ImportMixedTypes=OneDayWhen and trying to use the ISAM: you get the
error, 'Invalid setting in Excel key of the Engines section of the
Windows Registry.' The only valid values are:

ImportMixedTypes=Text
ImportMixedTypes=Majority Type

Data type is determined column by column. 'Majority Type' means a
certain number of rows (more on this later) in each column are scanned
and the data types are counted. Both a cell's value and format are
used to determine data type. The majority data type (i.e. the one with
the most rows) decides the overall data type for the entire column.
There's a bias in favor os numeric in the event of a tie. Rows from
any minority data types found that can't be cast as the majority data
type will be returned with a null value.

For ImportMixedTypes=Text, the data type for the whole column will be:

ImportMixedTypes=Text will curtail text at 255 characters as 'Memo' is
cast as 'Text'. For a column to be recognized as 'Memo', majority type
must be detected, meaning the majority of rows detected must contain
255 or more characters.

But how many rows are scanned for each column before is decided that
mixed types exist? There is a second registry Key, TypeGuessRows. This
can be a value from 0-16 (decimal). A value from 1 to 16 inclusive is
the number of rows to scan. A value of zero means all rows will be
scanned.

There is one final twist. If using ADO, a setting of IMEX=1 in the
connection string's extended property determines whether the
ImportMixedTypes value is honored. IMEX refers to IMport EXport mode.
There are three possible values. IMEX=0 and IMEX=2 result in
ImportMixedTypes being ignored and the default value of 'Majority
Types' is used. IMEX=1 is the only way to ensure ImportMixedTypes=Text
is honored. The resulting connection string might look like this:

Finally, although it is mentioned in MSDN articles that MAXSCANROWS
can be used in the extended properties of the connection string to
override the TypeGuessRows registry keys, this seems to be a fallacy.
Using MAXSCANROWS=0 in this way never does anything under any
circumstances. Put another way, is has just the same effect as putting
ONEDAYWHEN=0 in the extended properties, being none (not even an
error!). The same applied to ImportMixedTypes i.e. can't be used in
the connection string to override the registry setting.

In summary, use TypeGuessRows to get Jet to detect whether a 'mixed
types' situation exists or use it to 'trick' Jet into detecting a
certaint data type as being the majority type. In the event of a
'mixed types' situation being detected, use ImportMixedTypes to tell
Jet to either use the majority type or coerce all values as 'Text'
(max 255 characters).

Welcome to PC Review!

Hello and welcome to PC Review. We're a friendly computing community, bustling with knowledgeable members to help solve your tech questions.

Please join our friendly community by clicking the button below - it only takes a few seconds and is totally free. You'll be able to ask any tech support questions, or chat with the community and help others.
Ask a Question