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

Discussion in 'Microsoft Excel Misc' started by mnhesh, May 12, 2004.

  1. mnhesh

    mnhesh Guest

    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

    What's the solution for this?

    mnhesh, May 12, 2004
    1. Advertisements

  2. Hi

    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.
    Arvi Laanemets, May 12, 2004
    1. Advertisements

  3. mnhesh

    onedaywhen Guest

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

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


    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=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:

    Jet (MS Access UI): 'Text' data type
    DDL: VARCHAR(255)
    ADO: adWChar ('a null-terminated Unicode character string')

    Note that this is distinct from:

    Jet (MS Access UI): 'Memo' data type
    DDL: N/A
    ADO: adLongVarWChar ('a long null-terminated Unicode string value')

    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

    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:

    Data Source=C:\ db.xls;
    Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'

    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).

    onedaywhen, May 12, 2004
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.