topic DAO/ADO Returning Null on Excel File Integers in VBA Forum

DAO/ADO Returning Null on Excel File Integers

Anonymous — Tue, 11 Mar 2003 12:55:24 GMT

I'm finding that if I connect to a XLS file with DAO or ADO that
certain cells will return null when it should contain an integer.
I've tried formatting the cells as text in Excel but that don't help.
The null shows up as a variant in the fetched data collection even
before I attempt to assign it to a variable.

What does help is prefixing each integer with an ' in Excel. But
thats obviously not a practical thing to do.

Anyone ever run across this?

Thanks in advance.

Terry

Re: DAO/ADO Returning Null on Excel File Integers

Anonymous — Wed, 12 Mar 2003 04:13:50 GMT

I haven't seen that, but I haven't done much Excel work.

ADO and DAO ignore any data type format assignments in Excel. The first
8 rows are scanned to try to figure out the data type. This can cause
problems.

<> has
lots of useful information.

--
jrf
Member of the Autodesk Discussion Forum Moderator Program
Please do not email questions unless you wish to hire my services

In article <3E6E4D3C.E51FC2AD@dotsoft.com>, Terry W. Dotson wrote:
> I'm finding that if I connect to a XLS file with DAO or ADO that
> certain cells will return null when it should contain an integer.
> I've tried formatting the cells as text in Excel but that don't help.
> The null shows up as a variant in the fetched data collection even
> before I attempt to assign it to a variable.
>
> What does help is prefixing each integer with an ' in Excel. But
> thats obviously not a practical thing to do.
>
> Anyone ever run across this?
>
> Thanks in advance.
>
> Terry
>

Re:

Anonymous — Thu, 13 Mar 2003 08:34:54 GMT

Jon Fleming wrote:

> ADO and DAO ignore any data type format assignments in Excel. The first
> 8 rows are scanned to try to figure out the data type. This can cause
> problems.
>
> <> has
> lots of useful information.

Thanks for the pointer, as per this topic (referenced in the above)
...

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q194124

... adding IMEX=1; to the connect string solved the problem, since
luckily I have a read only need.

Thanks again!

Terry