Re: Problem to see specific Data in Excel Cell via VB6

From: Daniel Crichton (msnews_at_worldofspack.co.uk)
Date: 10/28/04 

Date: Thu, 28 Oct 2004 17:27:33 +0100

"eranRL" <eranRL@discussions.microsoft.com> wrote in message
news:B1E4E6B4-EF75-4E9F-9A0F-193DD7C11564@microsoft.com...
> Hello all,
>
> I have a very wierd problem.
>
> I have an Excel file, that looks normaly
>
> I have data in the cells. that I can see while activate the
> Excell Program, can manipulate that data... like Cut paste ac..
>
> But when I go into my VB project..
> Some of the Cells I see as a NULL value..
> Can somebody help me, do you have an Idea how can that happend????/

If the column you are reading contains mixed numeric and text data, then
it's possible you're seeing nulls in the cells that have non-numeric data in
them, especially if the first few rows have numeric data in that column. If
so, you need to change a couple of registry entries.

HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows:REG_DWORD:0

HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes:REG_SZ:Text

This will make the Excel Jet driver treat all columns of mixed data as text,
and the 0 for TypeGuessRows means that all rows in the Excel *** will be
scanned to determine if the data is mixed rather than just the first few.
This will slow down opening of large sheets, but I find that the compromise
of having missing data is much worse than a few seconds of additional
loading time. You'll also need to add

IMEX=1

to your Extended Properties parameter, ie:

pszConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & pszDataBase & _
"; Extended Properties='Excel 8.0;HDR=No;IMEX=1'"

(I have changed the double quotes to single quotes in the Extended
Properties, I'm not sure if it makes a difference but in the applications I
have using Excel data via ADO I use single quotes and they work fine).

Dan