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

    What's the solution for this?

    Thank

    --
     
    mnhesh, May 12, 2004
    #1
    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
    (When sending e-mail, use address arvil<At>tarkon.ee)



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

  3. mnhesh

    onedaywhen Guest

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

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

    Provider=Microsoft.Jet.OLEDB.4.0;
    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
    #3
    1. Advertisements

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.
Similar Threads
  1. Guest
    Replies:
    3
    Views:
    337
    Peo Sjoblom
    May 17, 2004
  2. Guest
    Replies:
    1
    Views:
    286
    Guest
    Feb 2, 2007
  3. Fred Ernst
    Replies:
    3
    Views:
    5,192
  4. Guest
    Replies:
    2
    Views:
    513
    Guest
    Jun 14, 2007
  5. Guest
    Replies:
    2
    Views:
    306
    Guest
    Jul 17, 2007
  6. Guest
    Replies:
    6
    Views:
    311
    Guest
    Oct 30, 2007
  7. Chaslefire
    Replies:
    0
    Views:
    371
    Chaslefire
    Jan 31, 2009
  8. gbryce
    Replies:
    0
    Views:
    280
    gbryce
    Feb 22, 2010
Loading...